CODEWITHSUNDEEP
sqldatabaset-sqlsql-server-2008
Madadi
Madadi

Reputation: 45

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.

Upvotes: 0

Views: 130

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 3

Thom A
Thom A

Reputation: 95561

This is a "simple" pivot. Personally, however, I prefer using a Cross Tab, rather than the restrictive PIVOT operator:

SELECT [Date],
       [Name],
       COUNT(CASE StateData WHEN 'OPENED' THEN 1 END) AS OPENED,
       COUNT(CASE StateData WHEN 'CLOSED' THEN 1 END) AS CLOSED,
       COUNT(CASE WHEN StateData IS NULL THEN 1 END) AS UNUSED
FROM YourTable
GROUP BY [Date],
         [Name];

Note, that the version of SQL Server you've tagged (2008) is now completely unsupported. It's therefore highly recommended you look at upgrade paths as soon as possible.

Upvotes: 2

Related Questions