我有SQL的基本知识,但我需要一些帮助在一个更复杂的查询。
我想获得每个不同的"状态"的总数:"活动"等待用户信息"|"关闭"|"新建"|"解决"
下面是我想展示的列/结果:按年过滤开票月份=2015,各状态合计计数
['Month' | 'Active' | ' waiting User Info' | 'Closed' | 'New' | 'Resolved']
我希望列是固定的,即使结果是0。
这是我的表
CREATE TABLE [dbo].[Incidents](
[Number] [varchar](50) NULL,
[DateOpened] [datetime] NULL,
[Severity] [varchar](50) NULL,
[Priority] [varchar](50) NULL,
[Status] [varchar](50) NULL
) ON [PRIMARY]
谢谢你的帮助!
select month(DateOpened),
sum(case when status = 'Active' then 1 else 0 end) as [Active],
sum(case when status = 'Awaiting User Info' then 1 else 0 end) as [Awaiting User Info],
sum(case when status = 'Closed' then 1 else 0 end) as [Closed],
sum(case when status = 'New' then 1 else 0 end) as [New],
sum(case when status = 'Resolved' then 1 else 0 end) as [Resolved]
from incidents
where year(DateOpened) = 2015
group by month(DateOpened)
如果您想要所有月份,即使没有特定月份的记录,那么您可以根据月份名称的联合列表左联接表
我想,它会帮助你的!
SELECT *
FROM (
SELECT
month([DateOpened]) as [month], [Status],
COUNT(*) as cnt
FROM [Incidents]
group by month([DateOpened]) , [Status]
) as s
PIVOT
(
SUM(cnt)
FOR [Status] IN ( Active , [Awaiting User Info] , [Closed] , [New] , [Resolved])
)AS pivotname
您应该尝试使用嵌套的SELECT
和GROUP BY
。COUNT
将确保您也捕获0值。注意字段和表别名:
SELECT X.M, A.Act as Active,
AUI.Await as Awaiting_User_Info,
C.Clo as Closed, N.Ne as New R.Res as Resolved
FROM (SELECT MONTH(DateOpened) as M
FROM Incidents
GROUP BY MONTH(DateOpened) ) X
LEFT JOIN
(SELECT MONTH(DateOpened) as M,
COUNT(*) AS Act
FROM Incidents
WHERE Status = 'Active'
GROUP BY M) A
ON X.M = A.M LEFT JOIN
(SELECT MONTH(DateOpened) as M,
COUNT(*) AS Await
FROM Incidents
WHERE Status = 'Awaiting User Info'
GROUP BY MONTH) AUI
ON X.M = AUI.M LEFT JOIN
(SELECT MONTH(DateOpened) as M,
COUNT(*) AS Clo
FROM Incidents
WHERE Status = 'Closed' GROUP BY MONTH) C
ON X.M = C.M LEFT JOIN
(SELECT MONTH(DateOpened) as M,
COUNT(*) AS Ne
FROM Incidents
WHERE Status = 'New'
GROUP BY MONTH) N
ON X.M = N.M LEFT JOIN
(SELECT MONTH(DateOpened) as M,
COUNT(*) AS Res
FROM Incidents
WHERE Status = 'Resolved'
GROUP BY MONTH) N
ON X.M = R.M