问题描述
我有一张如下所示的表格:
Hi I have a table that looks like the following:
Table Name: Emails
ID |CreatedDate |finalStatus
115173922 |2013-04-09 12:33:23.234 |DELIVERED
115123432 |2013-04-09 08:21:23.234 |FAILED
115143212 |2013-04-09 12:24:23.234 |DELIVERED
115173922 |2013-04-09 05:05:23.234 |DELIVERED
111233922 |2013-04-10 12:44:23.234 |PENDING
115123912 |2013-04-10 12:05:23.234 |DELIVERED
115173922 |2013-04-11 22:09:23.234 |DELIVERED
111233922 |2013-04-11 13:05:23.234 |PENDING
115123912 |2013-04-11 05:23:23.234 |DELIVERED
我需要做的是获取该月每天的 DELIVERED、FAILED 和 PENDING finalStatus 的总数.我试图修改人们在以前的答案中给出的 MySQL 代码,例如:用于计算每天总订单数的 SQL 查询? 但一直无法使其正常工作.
What I need to do is get the total amount of DELIVERED, FAILED and PENDING finalStatus's per day for the month. I have tried to modify MySQL code that people have given in previous answers such as this: SQL query for Calculating Total No. of Orders per Day? but have not been able to get it working.
这是我到目前为止的代码:
Here is the code that I have so far:
SELECT DISTINCT (CAST(CreatedDate as DATE)) as Date,
(SELECT COUNT(finalStatus)
FROM [Emails]
WHERE finalStatus = 'DELIVERED') AS Delivered,
(SELECT COUNT(finalStatus)
FROM [Emails]
WHERE finalStatus = 'FAILED') AS Failed,
(SELECT COUNT(finalStatus)
FROM [Emails]
WHERE finalStatus = 'PENDING') AS Pending
FROM [Emails]
GROUP BY (CAST(CreatedDate as DATE))
如果有人能帮助我,那就太棒了.我已经坚持了几个小时,可能很快就会发疯......
If anyone could help me that would be amazing. I have been stuck on this for a few hours now and may go crazy soon...
推荐答案
由于这是 SQL Server 2008
,所以利用将 CREATEDDATE
转换为 DATE
只使用 CAST()
,
Since this is SQL Server 2008
, make use of casting the CREATEDDATE
into DATE
only using CAST()
,
SELECT CAST(E.CreatedDate AS DATE) DateCreated,
COUNT(case when E.finalStatus = 'DELIVERED' then 1 end) as DELIVERED,
COUNT(case when E.finalStatus = 'FAILED' then 1 end) as FAILED,
COUNT(case when E.finalStatus = 'PENDING' then 1 end) as PENDING
FROM TableName E
GROUP BY CAST(E.CreatedDate AS DATE)
http://www.sqlfiddle.com/#!3/dc195/4
这篇关于如何获得每天的总数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!