SELECT count based on columns value

I have a table like this

Date  | Name  | StateData |
------+-------+-----------+    
xxxxx | Tom   | OPENED    |
xxxxx | David | NULL      |
xxxxx | Tom   | NULL      |
xxxxx | Brand | CLOSED    |
xxxxx | Brand | NULL      |
xxxxx | Brand | OPENED    |

What result I want to achieve is something like this

Date | Name | OPENED | CLOSED | UNUSED |
-----+------+--------+--------+--------+
xxxxx| Tom  |   1    |    0   |    1   |
xxxxx| David|   0    |    0   |    1   |
xxxxx| Brand|   1    |    1   |    1   |

I’ve tried something like this

SELECT 
    Name,
    [OPENED] = COUNT(CASE WHEN StateData ='OPENED' THEN StateData END),
    [CLOSED] = COUNT(CASE WHEN StateData ='CLOSED' THEN StateData END),
    [UNUSED] = COUNT(CASE WHEN StateData IS NULL THEN StateData END)
FROM 
    [dbo].[StateData]
GROUP BY
    Name

the result is that I don’t have at least duplicate records in Name column, but I can clearly see with simple select count(*) that the counts in the columns are not right.

First of all I did google some samples, and made the SELECT above.

Answer

I would use SUM() instead. You have a problem with NULL:

SELECT Name,
       SUM(CASE WHEN StateData = 'OPENED' THEN 1 ELSE 0 END) as opened
       SUM(CASE WHEN StateData = 'CLOSED' THEN 1 ELSE 0 END) as closed
       SUM(CASE WHEN StateData IS NULL THEN 1 ELSE 0 END) as unused
FROM [dbo].[StateData]
GROUP BY Name;

Your unused will always be zero because COUNT(NULL) is always zero.

Advertisement

Answer