SQL与多个计数来自同一列中选择
问题描述:
我有一个表中的SQL有以下栏目:状态,CreateDateTime和案例类型SQL与多个计数来自同一列中选择
10行数据的示例如下所示:
Resolved-Withdrawn 2017-08-28 10:20:58.820 Termination Quote DE
Pending-Customer 2017-08-28 10:44:37.987 Termination Quote DE
Pending-Customer 2017-08-28 10:44:39.953 Termination Quote DE
Pending-Customer 2017-08-28 10:51:55.643 Termination Quote DE
Pending-Customer 2017-08-28 10:51:56.513 Termination Quote DE
Pending-Customer 2017-08-28 10:54:20.160 Termination Quote DE
Pending-Customer 2017-08-28 10:54:20.747 Termination Quote DE
Pending-Customer 2017-09-10 06:12:52.113 Termination Quote DE
Pending-Customer 2017-09-15 12:34:32.657 Termination Quote DE
我想实现以下目标:
Status Total_August Total_September
-----------------------------------------------------
Resolved-Withdrawn 1 0
Pending-Customer 6 2
我将如何创建SQL中的这个查询?
我最初的想法是这样的(这显然是行不通的),但也许它可以让你更好的理解我的思维过程,作为一个非经验的用户。
SELECT Status, COUNT(pxCreateDateTime) as Total_August, COUNT(pxCreateDateTime) as Total_September
FROM ContractMgtWork
WHERE MONTH(pxCreateDateTime) = 8 and YEAR(pxCreateDateTime) = 2017 AND
CaseType = 'Amortization Schedule DE'
GROUP BY Status
答
count
,像许多其他聚合函数,跳过null
秒。您可以通过计数case
表达任何你想利用这个属性来计算:
SELECT Status,
COUNT(CASE WHEN MONTH(pxCreateDateTime) = 8 THEN 1 END) AS Total_August,
COUNT(CASE WHEN MONTH(pxCreateDateTime) = 9 THEN 1 END) AS Total_September
FROM ContractMgtWork
WHERE YEAR(pxCreateDateTime) = 2017 AND
CaseType = 'Amortization Schedule DE'
GROUP BY Status
啊,这也正是我想要的,谢谢:) – R100