如何获得每天的总数

问题描述

我有一张如下所示的表格:

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