这是我的表员工:
员工表有三列,如下所示:
ID yearquarter Amount
7 20171 500
7 20172 300
7 20133 100
7 20174 200
7 20181 900
7 20182 500
7 20183 100
需要将一年中的所有四个季度金额相加,输出应为单行汇总总和如下所示。如果任何一年只有四分之三,仍然需要将三个相加四分之一并将它们作为输出放在单行中
输出:
ID yearquarter Amount
7 20171 1000
7 20181 1500
我正在尝试使用rank((函数,但仍然遇到问题:
SELECT case when rank<=rank+3 then sum(amount) end as charges, max(yearquarter),id
FROM (
SELECT RANK() OVER (partition by id order by yearquarter) Rank, amount,yearquarter,id
FROM sample where id=10004)a
GROUP BY id
order by id
只需按id
和年份聚合:
select id, left(yearquarter, 4) as yyyy, sum(amount)
from sample
group by id, left(yearquarter, 4)
order by yyyy;