CODEWITHSUNDEEP
sqlnullcase
user1508682
user1508682

Reputation: 1371

SQL Case with no null

When I run this query:

select
    '1992' = CASE WHEN Year(HireDate) = 1992 THEN count(*) END,
    '1993' = CASE WHEN Year(HireDate) = 1993 THEN count(*) END,
    '1994' = CASE WHEN Year(HireDate) = 1994 THEN count(*) END
  from employees
 group by Year(HireDate)

I get:

1992     1993      1994
3        NULL      NULL
NULL      3        NULL
NULL     NULL       3

How can I change the query to get:

1992    1993    1994
3           3      3

Upvotes: 1

Views: 143

Answers (2)

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

select 
  '1992' = sum (case when year(HireDate) = 1992 THEN 1 else 0 END),
  '1993' = sum (case when year(HireDate) = 1993 THEN 1 else 0 END),
  '1994' = sum (case when year(HireDate) = 1994 THEN 1 else 0 END),
from employees

Upvotes: 1

dani herrera
dani herrera

Reputation: 51645

With this query you will get only a row:

select 
'1992'=
count( CASE WHEN Year(HireDate) = 1992 THEN 1 END ),
'1993' =
count( CASE WHEN Year(HireDate) = 1993 THEN 1 END ),
'1994' = 
count( CASE WHEN Year(HireDate) = 1994 THEN 1 END )
from employees

Upvotes: 3

Related Questions