尝试在同一查询中使用指定不同日期范围的不同 where 子句对一个字段"Count"三次求和？

Question

SELECT DeviceID as "Printer Name",
    (SELECT SUM(Count) WHERE DATE >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)as "7 Day Count",
    (SELECT SUM(Count) WHERE DATE >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)as "30 Day Count"
 FROM printer_stats.Statistics
 GROUP BY DeviceID

这是不工作，需要一些输入，下一步尝试什么

Answer 1

使用SUM(CASE WHEN condition THEN Count ELSE 0 END):

SELECT
    DeviceID as "Printer Name",
    SUM(CASE WHEN DATE >= CURDATE() - INTERVAL 7 DAY THEN Count ELSE 0 END) AS "7 Day Count",
    SUM(CASE WHEN DATE >= CURDATE() - INTERVAL 30 DAY THEN Count ELSE 0 END) AS "30 Day Count",
FROM printer_stats.Statistics
WHERE DATE >= CURDATE() - INTERVAL 30 DAY
GROUP BY DeviceID