从一个表中分组SQL列

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)

注意:列Status有3个可能的值(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.

我期待的输出,也应按年份排序。例如,我调用2015年的所有这些记录,我使用变量@Year。

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

注意:列Total是Cleaned和Unclean的SUM

   +---------+-----------+-----------+----------+---------+
   |  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.

我似乎无法获得正确的代码,因为sql这是我当前的代码。

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

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

我知道我的编码是错误的,因为它不完整。

2 个解决方案

#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:

首先生成1到12之间的数字列表以保存所有月份。然后对雇佣人员进行LEFT JOIN以确保所有缺失的月份都被计算在内。然后对总计使用条件聚合:

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:

如果您想过滤年份,比如@year = 2015,您可以将以前的ctes替换为:

;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
)...

#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:

我建议创建TEMP表,其值为1到12(月数),并使用TEMP表加入表。要将值实现为列名,可以使用PIVOT或CASE。你可以在下面这样做:

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

您可以在以下位置测试它:SQL FIDDLE