mysql - MySQL:根据帐单日期后的多少天显示总费用

我想达到什么目的？
Expected Result Screenshot
说明：要了解由于帐单日期已过而应支付的金额的概述。这里的0-15表示自帐单日期起15天，16-30表示自帐单日期已过去16到30天，我想知道这些范围内应到期的金额以及最后的总计。

涉及的表数：1
表：用户表
相关列：
帐单日期
待定金额

我使用的查询：

SELECT (CASE
        WHEN DATEDIFF(NOW(), bill_date) BETWEEN 0 AND 15 THEN '0-15'
        WHEN DATEDIFF(NOW(), bill_date) BETWEEN 16 AND 30 THEN '16-30'
        WHEN DATEDIFF(NOW(), bill_date) BETWEEN 31 AND 45 THEN '31-45'
        WHEN DATEDIFF(NOW(), bill_date) BETWEEN 46 AND 60 THEN '46-60'
        WHEN DATEDIFF(NOW(), bill_date) BETWEEN 61 AND 90 THEN '61-90'
        WHEN DATEDIFF(NOW(), bill_date) BETWEEN 91 AND 180 THEN '91-180'
        WHEN DATEDIFF(NOW(), bill_date) BETWEEN 181 AND 365 THEN '181-365'
        WHEN DATEDIFF(NOW(), bill_date) >= 366 THEN '> 365
            END
        ) as bill_range,
        SUM(pending_amount) as amount_due,
FROM
    `user`
    GROUP BY bill_range

结果我得到：
Current result I got

如何取得结果？

最佳答案

您的CASE语句仅创建1列。
您需要条件聚合：

SELECT
  SUM(CASE WHEN DATEDIFF(NOW(), bill_date) BETWEEN 0 AND 15 THEN pending_amount ELSE 0 END) `0-15`,
  SUM(CASE WHEN DATEDIFF(NOW(), bill_date) BETWEEN 16 AND 30 THEN pending_amount ELSE 0 END) `16-30`,
  SUM(CASE WHEN DATEDIFF(NOW(), bill_date) BETWEEN 31 AND 45 THEN pending_amount ELSE 0 END) `31-45`,
  SUM(CASE WHEN DATEDIFF(NOW(), bill_date) BETWEEN 46 AND 60 THEN pending_amount ELSE 0 END) `46-60`,
  SUM(CASE WHEN DATEDIFF(NOW(), bill_date) BETWEEN 61 AND 90 THEN pending_amount ELSE 0 END) `61-90`,
  SUM(CASE WHEN DATEDIFF(NOW(), bill_date) BETWEEN 91 AND 180 THEN pending_amount ELSE 0 END) `91-180`,
  SUM(CASE WHEN DATEDIFF(NOW(), bill_date) BETWEEN 181 AND 365 THEN pending_amount ELSE 0 END) `181-365`,
  SUM(CASE WHEN DATEDIFF(NOW(), bill_date) >= 366 THEN pending_amount ELSE 0 END) `> 365`,
  SUM(pending_amount) `grand total`
FROM `user`

关于mysql - MySQL:根据帐单日期后的多少天显示总费用，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57576920/