如何将特定列与月份和年份相加 [英] How to sum of particular column with month and year wise

问题描述

我的表中有以下数据:

uniqueId    d_date      amount
1         2018-02-01    100.25
2         2019-03-01    456.5
3         2018-02-01    455
4         2019-05-01    200.48
5         2018-06-01    100
6         2019-07-01    200
7         2018-12-01    6950

现在我想要这样的输出:

Now i want output like :

Year   Jan  Feb     Mar    Apr  May      Jun  July  Aug Sept Oct Nov Dec     Total
2018    -   555.25   -      -   -        100   -     -   -    -   -  6950   7605.25
2019    -   -       456.5   -   200.48    -     200  -   -    -   -  -      856.98

我该怎么做?

推荐答案

您可以使用条件聚合进行透视:

You can pivot with conditional aggregation:

select
    year(d_date) yr,
    sum(case when month(d_date) = 1 then amount end) Jan,
    sum(case when month(d_date) = 2 then amount end) Feb,
    sum(case when month(d_date) = 3 then amount end) Mar,
    ...
    sum(case when month(d_date) = 12 then amount end) Dec,
    sum(amount) total
from mytable
group by year(d_date)
order by yr

这篇关于如何将特定列与月份和年份相加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！