Geek Answers Handbook

Grouping SQL columns from one table

Currently, it is difficult for me to get the correct values ​​from my table. Here is my table

NOTE. The Status column 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 result that I expect and should also be sorted by year. For example, I call all these records for 2015, which I get with the @Year variable.

NOTE. Total column is 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 cannot get the correct code since sql is my current code.

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

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

+5
source share
2 answers

Generate a list of numbers from 1 to 12 first to save all month s. Then do LEFT JOIN on Hires to make sure all missing months are counted. Then use conditional aggregation for 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 mN = MONTH(h.HireDate) --AND YEAR(h.HireDate) = @year --uncomment this line to filter for year. GROUP BY mN ORDER BY mN

If you want to enable 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, mN - 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 the year, say @year = 2015 , you can replace the previous ctes as follows:

 ;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, mN - 1, DATEADD(YEAR, @year - 1900, 0)) FROM CteMonths m )...
+3
source

I suggest creating a TEMP table with values ​​from 1 to 12 (number of months) and JOIN your table with a TEMP table. To achieve values ​​as column names, you can use PIVOT or CASE . You can do this in the 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

0
source

More articles:


All Articles