how to sum columns group by different date in sql server

Question

I've been trying to create a summary report which will sum total for the week and group it by date. Please what i'm doing wrong

SELECT 
 SUM(Numb1) AS [ Registration], 
 SUM(Numb2) AS [RENEWAL], 
 SUM(Penalty) AS PENALTY, 
 SUM(MTR), 
 SUM(numb3) AS PH,
 SUM(Insurance) AS INSURANCE, 
 SUM(nub4) AS [data received], 
 SUM(Numb1)+ SUM(Numb2)+ SUM(Penalty)+ SUM(MTR), SUM(numb3) + SUM(Insurance)+ SUM(nub4)  AS [TOTAL SUM]
FROM dbo.Vehicle_Transactions
GROUP BY transaction_date

You should also select the transaction_date, shouldn't you? Also note that if the transaction_date is actually a datetime, it will not work, you'll need to cast it to date only. You can find reference how to do that [here](http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype) — Tomas Pastircak, Apr 22 '14 at 15:01

score 1 · Accepted Answer · answered Apr 22 '14 at 15:07

You probably want to group by week, not by day. I also added a year, to make the query work for each year (otherwise some lines will became ambiguous with entries from e.g. 2013 and 2014):

SELECT 
 DATEPART(wk,transaction_date) Week,
 DATEPART(yy,transaction_date) Year, --to make sure it'll work any year
 MIN(transaction_date),
 SUM(Numb1) AS [ Registration], 
 SUM(Numb2) AS [RENEWAL], 
 SUM(Penalty) AS PENALTY, 
 SUM(MTR), 
 SUM(numb3) AS PH,
 SUM(Insurance) AS INSURANCE, 
 SUM(nub4) AS [data received], 
 SUM(Numb1)+ SUM(Numb2)+ SUM(Penalty)+ SUM(MTR), 
 SUM(numb3) + SUM(Insurance)+ SUM(nub4)  AS [TOTAL SUM]
FROM dbo.Vehicle_Transactions
GROUP BY DATEPART(WK,transaction_date), DATEPART(yy,transaction_date)

@user3560881 You are welcome, but if the query answered your question, you can simply accept the answer :) — Tomas Pastircak, Apr 24 '14 at 22:06

how to sum columns group by different date in sql server

1 Answers1