Grouping SQL columns from one table

Question

I am currently having difficulty getting the correct values from my table. Here is my table

NOTE: The column Status has 3 possible values (Cleaned, Unclean, Closed)

    +-----------+-------------+--------+------------+
    |ApplicantID|ApplicantName| Status |  HireDate  |
    +-----------+-------------+--------+------------+
    |     1     | John Smith  |Cleaned |08/26/2015  |
    |     2     | Alex Murphy |Closed  |09/12/2015  |
    |     3     | Oliver David|Cleaned |01/11/2015  |
    |     4     | Max Payne   |Unclean |03/18/2015  |
    +-----------+-------------+--------+------------+

The output I'm expecting and it should also be sorted by year. For example I call all these records for the year 2015 which I get using the variable @Year.

NOTE: The column Total is the SUM of Cleaned and Unclean

   +---------+-----------+-----------+----------+---------+
   |  Month  |  Cleaned  |  Unclean  |  Closed  |  Total  |
   +---------+-----------+-----------+----------+---------+
   |  January|     1     |     0     |    0     |    1    |
   | February|     0     |     0     |    0     |    0    |
   |  March  |     0     |     1     |    0     |    1    |
   |  April  |     0     |     0     |    0     |    0    |
   |  May    |     0     |     0     |    0     |    0    |
   |  June   |     0     |     0     |    0     |    0    |
   |  July   |     0     |     0     |    0     |    0    |
   |  August |     1     |     0     |    0     |    1    |
   |September|     0     |     0     |    1     |    0    |
   |  October|     0     |     0     |    0     |    0    |
   | November|     0     |     0     |    0     |    0    |
   | December|     0     |     0     |    0     |    0    |
   +---------+-----------+-----------+----------+---------+

I can't seem to get the right code, for the sql this is my current code.

SELECT Month(HireDate) AS Month, COUNT(*) 
FROM Hires 
GROUP BY Month(HireDate)

I know my coding is wrong, because it is incomplete.

Answer 1

Generate a list of numbers from 1 to 12 first to hold all months. Then do a LEFT JOIN on Hires to make sure all missing months are accounted for. Then use conditional aggregation for the totals:

SQL Fiddle

;WITH CteMonths AS(
    SELECT * FROM(VALUES
        (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
    )t(N)
)
SELECT
    Month   = DATENAME(MONTH, DATEADD(MONTH, N-1,0)),
    Cleaned = SUM(CASE WHEN h.Status = 'Cleaned' THEN 1 ELSE 0 END),
    Closed  = SUM(CASE WHEN h.Status = 'Closed' THEN 1 ELSE 0 END),
    Unclean = SUM(CASE WHEN h.Status = 'Unclean' THEN 1 ELSE 0 END),
    Total   = SUM(CASE WHEN h.Status IN('Cleaned', 'Unclean') THEN 1 ELSE 0 END)
FROM CteMonths m
LEFT JOIN Hires h
    ON m.N = MONTH(h.HireDate)
    --AND YEAR(h.HireDate) = @year --uncomment this line to filter for year.
GROUP BY m.N
ORDER BY m.N

---

If you want to include the YEAR:

SQL Fiddle

;WITH CteMonths AS(
    SELECT * FROM(VALUES
        (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
    )t(N)
),
CteYears(yr) AS(
    SELECT DISTINCT YEAR(HireDate) FROM Hires
),
CteAllDates(dt) AS(
    SELECT
        DATEADD(MONTH, m.N - 1, DATEADD(YEAR, y.yr - 1900, 0))
    FROM CteMonths m
    CROSS JOIN CteYears y
)
SELECT
    Year    = YEAR(d.dt),
    Month   = DATENAME(MONTH, d.dt),
    Cleaned = SUM(CASE WHEN h.Status = 'Cleaned' THEN 1 ELSE 0 END),
    Closed  = SUM(CASE WHEN h.Status = 'Closed' THEN 1 ELSE 0 END),
    Unclean = SUM(CASE WHEN h.Status = 'Unclean' THEN 1 ELSE 0 END),
    Total   = SUM(CASE WHEN h.Status IN('Cleaned', 'Unclean') THEN 1 ELSE 0 END)
FROM CteAllDates d
LEFT JOIN Hires h
    ON MONTH(d.dt) = MONTH(h.HireDate)
    AND YEAR(d.dt) = YEAR(h.HireDate)   
GROUP BY YEAR(d.dt), MONTH(d.dt), DATENAME(MONTH, d.dt)
ORDER BY YEAR(d.dt), MONTH(d.dt)

If you want to filter for year, say @year = 2015, you can replace the previous ctes with:

;WITH CteMonths AS(
    SELECT * FROM(VALUES
        (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
    )t(N)
),
CteAllDates(dt) AS(
    SELECT
        DATEADD(MONTH, m.N - 1, DATEADD(YEAR, @year - 1900, 0))
    FROM CteMonths m
)...

Answer 2

I suggest to create TEMP table with values from 1 to 12 (numbers of months) and JOIN your table with TEMP table. To achieve values as columns names you can use PIVOT or CASE. You can do It in following:

INSERT INTO #Months VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)

SELECT DATENAME(MONTH, DATEADD(MONTH, m.Id-1, 0)) AS [Month]
       , SUM(CASE WHEN [Status] = 'Cleaned' THEN 1 ELSE 0 END) AS [Cleaned] 
       , SUM(CASE WHEN [Status] = 'Closed' THEN 1 ELSE 0 END ) AS [Closed] 
       , SUM(CASE WHEN [Status] = 'Unclean' THEN 1 ELSE 0 END) AS [Unclean] 
       , SUM(CASE WHEN [Status] IN ('Unclean', 'Cleaned') THEN 1 ELSE 0  END) AS [Total]
FROM #Test t
RIGHT JOIN #Months m ON m.Id = MONTH(t.HireDate)
GROUP BY m.Id

---

OUTPUT

   +---------+-----------+-----------+----------+---------+
   |  Month  |  Cleaned  |  Unclean  |  Closed  |  Total  |
   +---------+-----------+-----------+----------+---------+
   | January |     1     |     0     |    0     |    1    |
   | February|     0     |     0     |    0     |    0    |
   |  March  |     0     |     1     |    0     |    1    |
   |  April  |     0     |     0     |    0     |    0    |
   |  May    |     0     |     0     |    0     |    0    |
   |  June   |     0     |     0     |    0     |    0    |
   |  July   |     0     |     0     |    0     |    0    |
   |  August |     1     |     0     |    0     |    1    |
   |September|     0     |     0     |    1     |    0    |
   | October |     0     |     0     |    0     |    0    |
   | November|     0     |     0     |    0     |    0    |
   | December|     0     |     0     |    0     |    0    |
   +---------+-----------+-----------+----------+---------+

---

DEMO

You can test It at: SQL FIDDLE