Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 114
  • Last Modified:

Query not summing values by year

Why isn't this SQL in query designer summing the donations by family by year?

SELECT [Family Information].ID, Donations.[Date of Donation], Sum(Donations.[Donation Amount]) AS [SumOfDonation Amount]
FROM [Family Information] RIGHT JOIN Donations ON [Family Information].ID = Donations.[Family Name]
GROUP BY [Family Information].ID, Donations.[Date of Donation]
ORDER BY [Family Information].ID, Donations.[Date of Donation];
0
SteveL13
Asked:
SteveL13
1 Solution
 
Shaun KlineLead Software EngineerCommented:
Because you are grouping on Date of Donation, each day is grouped. If you want to group by year, add the YEAR function to both the GROUP BY and the SELECT clause.
0
 
fiboCommented:
Main source of problem: you are grouping not by year but by date (ie with resolution of teh second or less)

Suggested solution:
Not sure how to extract year from date in your own SQL dialect. With the examples at https://msdn.microsoft.com/en-us/library/ms174395.aspx  , your query could be something like
SELECT [Family Information].ID, Donations.[Date of Donation], 
DATENAME(year,  Donations.[Date of Donation]) AS DonYear,
Sum(Donations.[Donation Amount]) AS [SumOfDonation Amount]
FROM [Family Information] RIGHT JOIN Donations ON [Family Information].ID = Donations.[Family Name]
GROUP BY [Family Information].ID, DATENAME(year,  Donations.[Date of Donation])
ORDER BY [Family Information].ID, DATENAME(year,  Donations.[Date of Donation]) , Donations.[Date of Donation]; 

Open in new window

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now