SUM total numbers by day?

Question

If I have a database with two columns, Date and a Number, with multiple Number entries per Date.

How can I add a column that shows the total Number for the Date listed?

So if I have four entries for 05/09/2017, with values 2, 4, 3, 8, all four entries for that day should also have a column showing 17.

which version of sql-server are you using? – Vamsi Prabhala May 11 '17 at 16:54 — Vamsi Prabhala, May 11 '17 at 16:54

score 3 · Accepted Answer · answered May 11 '17 at 16:55

3

If you are on a SQL Server version 2012 and above, use the SUM window function.

select date,number,sum(number) over(partition by date) as total
from tablename

Otherwise use

select t1.date,t1.number,t2.total
from tablename t1
join (select date,sum(number) as total from tablename group by date) t2 on t1.date=t2.date

answered May 11 '17 at 16:55

Vamsi Prabhala

Only after the 3rd of 4th read did I understand the question +1 for the solution and understanding the question. – John Cappelletti May 11 '17 at 17:03

score 0 · Answer 2 · answered May 11 '17 at 17:13

0

Try this

SELECT datecolumn,SUM(number) AS total FROM tablename GROUP BY DATE(datecolumn)

answered May 11 '17 at 17:13

Keval Pithva

2 Answers2