• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 117
  • 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
 
Bernard S.CTOCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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