Mysql根据条件计算列值

Question

我有一个表，其中包含 day、month、year、total_payments 列。而且我必须根据天、月、年来计算total_payments。

如何计算值？

我在想这样的代码:

select 
    month, year, sum(total_payments) 
from 
    webassignment.subscription_stats 
group by 
    day;

select 
    month, year, sum(total_payments) 
from 
    webassignment.subscription_stats 
group by 
    month;

select 
    month, year, sum(total_payments) 
from 
    webassignment.subscription_stats 
group by 
    year;

但它不会返回正确答案。我想按天、按月、按年计算 total_payments。请帮我找到值(value)。

示例输入:

Day Month   Year    Total_payments
10  01      2008    10
10  01      2008    20
11  02      2008    10
10  03      2010    10

输出:

日间:

day month year total_payments
-----------------------------
10  01    2008 30
11  02    2008 10
10  03    2010 10

同月同年

Answer 1

您可以使用 GROUP BY WITH ROLLUP 获得按年、月和日汇总的汇总:

SELECT
    `Year`,
    `Month`,
    `Day`,
    SUM(`Total_payments`) as `Totals`
FROM `webassignment`.`subscription_stats`
GROUP BY `Year`,`Month`,`Day` WITH ROLLUP;

如果你想单独查询年、月、日:

按年份:

SELECT
    `Year`,
    SUM(`Total_payments`) as `Totals`
FROM `webassignment`.`subscription_stats`
GROUP BY `Year`
ORDER BY `Year;

按月:

SELECT
    `Year`,
    `Month`,
    SUM(`Total_payments`) as `Totals`
FROM `webassignment`.`subscription_stats`
WHERE `Year` = 2017
GROUP BY `Year`,`Month`
ORDER BY `Year`,`Month`;

按天:

SELECT
    `Year`,
    `Month`,
    `Day`,
    SUM(`Total_payments`) as `Totals`
FROM `webassignment`.`subscription_stats`
GROUP BY `Year`,`Month`,`Day`
ORDER BY `Year`,`Month`,`Day`;