ckoch ckoch - 4 months ago 10
SQL Question

Need to count unique occurrences in a column, and group the result by date

I need to count the unique instances in a field, then sort the counts by their dates and am trying to do it all in one query using SQL.

I have table that looks like this:

Date...........Defect_Code..........
2016-06-12 Machine Issue
2016-06-12 Broken
2016-06-12 null
2016-06-13 Machine Issue
2016-06-13 Machine Issue
2016-06-14 Crack
2016-06-14 Crack


I would like for this to be sorted by date while counting the instance of each Defect_Code resulting in a table looking similar to

Date............Count(Machine Issue)...Count(Broken)...Count(Crack)...Count(null)....
2016-06-12......1......................1...............0..............1..............
2016-06-13......2......................0...............0..............0..............
2016-06-14......0......................0...............3..............0..............


Thanks in advance for any help!

Answer

You can do this with conditional aggregation since the values for Defect_Code are consistent:

SELECT Date
     , COALESCE(SUM(CASE WHEN Defect_Code = 'Machine Issue' THEN 1 END),0) AS 'Count(Machine Issue)'
     , COALESCE(SUM(CASE WHEN Defect_Code = 'Broken' THEN 1 END),0) AS 'Count(Broken)'
     , COALESCE(SUM(CASE WHEN Defect_Code = 'Crack' THEN 1 END),0) AS 'Count(Crack)'
     , COALESCE(SUM(CASE WHEN Defect_Code IS NULL THEN 1 END),0) AS 'Count(Null)'
FROM YourTable
GROUP BY Date
ORDER BY Date