CODEWITHSUNDEEP
sqlsql-servert-sql
Sulaiman
Sulaiman

Reputation: 35

Aggregation in same row

I need some help with performing an aggregation. I have the following statement: 

SELECT name,
CASE WHEN year=2013 THEN count(ID) END AS 'Count2013',
CASE WHEN year=2012 THEN count(ID) END AS 'Count2012'
FROM Table GROUP BY year, name

and get the results as follows:

Name    Count2013        Count2012
ABC     NULL             14731
ABC     456              NULL
DEF     NULL             5666
DEF     14439            NULL

How can I get the following results?

Name    Count2013    Count2012
ABC     456          14731
DEF     14439        5666

Note, that I am trying to display results in same row without NULL.

Upvotes: 0

Views: 60

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

You just need to use a case expression.

SELECT name
    , sum(CASE WHEN year = 2013 THEN 1 ELSE 0 END) AS 'Count2013'
    , sum(CASE WHEN year = 2012 THEN 1 ELSE 0 END) AS 'Count2012'
FROM Table GROUP BY name

Upvotes: 2

Related Questions