問題描述

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

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 的總數(shù).我試圖修改人們在以前的答案中給出的 MySQL 代碼，例如:用于計算每天總訂單數(shù)的 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))

如果有人能幫助我，那就太棒了.我已經(jīng)堅持了幾個小時，可能很快就會發(fā)瘋......

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 轉(zhuǎn)換為 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

這篇關(guān)于如何獲得每天的總數(shù)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！