Calculate the sum for each month

Question

Hello.

I have the following table.

Date               Value

20230302      8

20230316      10

20230325      3

20230410      6

20230415      6

20230426      2

---

Expect results:

---

Can someone help me?

Answer 1

Hi @sleep

You can try this query.

create table test(Date datetime,Value int);
insert into test values
('20230302',8),
('20230316',10),
('20230325',3),
('20230410',6),
('20230415',6),
('20230426',2);

;with CTE as(
  select eomonth(Date) as Date,sum(Value) as sum from test group by eomonth(Date))
select year(Date) as year,month(Date) as month,sum from CTE;

Output:

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.