在聚合查詢中獲取聚合子值

我有一個查詢需要在已經聚合的外部查詢中顯示聚合值。

考慮一個查詢，該查詢獲取某一天被記入貸方的帳戶總數。

我想顯示

1. 訂單總數
2. “待處理”的訂單總數
3. “打開”的訂單總數

這是似乎有意義的查詢，但如下結果集所示，我顯然沒有使用正確的方法。“OPEN”列顯示整個總數，“PENDING”列保持 NULL。

我覺得我可能使用了錯誤的方法，有哪些選擇可以完成這項工作？

SELECT s.created, COUNT(s.id) 'total_accounts_credited', SUM(s.withdrawal) 'total_amount_credited', 
(select count(s.id) WHERE s.status_id = 'OPEN')  total_open_credited,
(select count(s.id) WHERE s.status_id = 'PENDING') total_pending_credited
 FROM statements s 
WHERE s.status_id in ('OPEN', 'PENDING')
GROUP BY 
 YEAR(s.created), 
 MONTH(s.created), 
 DAY(s.created) 
 ORDER BY s.created DESC
;

結果：

+---------------------+-------------------------+-----------------------+---------------------+----------------------+
| created             | total_accounts_credited | total_amount_credited | total_open_credited | total_pending_credited |
+---------------------+-------------------------+-----------------------+---------------------+----------------------+
| 2019-01-19 00:00:00 |                   18050 |              20813.18 |               18050 |                 NULL |
| 2019-01-12 00:00:00 |                   18135 |              24768.43 |               18135 |                 NULL |
| 2019-01-10 09:00:27 |                      80 |               1497.75 |                  80 |                 NULL |
| 2019-01-09 09:20:55 |                      51 |                933.50 |                  51 |                 NULL |
| 2019-01-08 16:45:14 |                      10 |                187.50 |                  10 |                 NULL |
| 2019-01-05 18:21:00 |                   17588 |              16968.49 |               17588 |                 NULL |
| 2018-12-29 00:00:00 |                   17893 |              25404.18 |               17893 |                 NULL |
| 2018-12-28 15:23:04 |                       1 |                 35.00 |                   1 |                 NULL |
| 2018-12-22 00:00:00 |                   17353 |              17048.18 |               17353 |                 NULL |
| 2018-12-15 00:00:00 |                   16893 |              10181.34 |               16893 |                 NULL |
| 2018-12-08 00:00:00 |                   16220 |              99547.09 |               16220 |                 NULL |
| 2018-12-01 00:00:00 |                   15476 |              87699.59 |               15476 |                 NULL |
+---------------------+-------------------------+-----------------------+---------------------+----------------------+

SELECT  ...,
       SUM(s.status_id = 'OPEN') AS total_open_credited,
       ...
   FROM ...
   WHERE ...
   GROUP BY DATE(s.created)   -- a simplification
   ORDER BY ...

說明： s.status_id = 'OPEN'是一個布爾表達式，計算結果為 TRUE 或 FALSE。真為 1；FALSE 為 0。然後SUM正確計算您的要求。

引用自：https://dba.stackexchange.com/questions/227576