CODEWITHSUNDEEP
sql
RedDeadVolvo
RedDeadVolvo

Reputation: 63

Need to find total sum of Amount column

I have been working on this query for most of the morning, using Stack Overflow quite a bit and trying different things. This has gone through many iterations. I am posting the step where I am stuck, and I know what's wrong, I just can't seem to figure out how to fix it.

Here is the issue:

I need to return the total amount donated between the given date ranges. I thought having the SUM in the select would do it, but when I add the condition Amount >= '4000' to the WHERE clause, it returns the sum of all donations over 4000, not the sum of all donations where the TOTAL is over 4000. I feel like I need to have it perform a sub-query, but I don't know where to put it. I could also be completely wrong and using the incorrect syntax for what I'm trying to achieve.

Code is below. Thank you!

SELECT  do.[donorId] as 'ID', 
        d.[fn] as 'First Name',
        d.[ln] as 'Last Name',
        SUM(do.[amount]) as Amount

FROM    [OURDB].[dbo].[Donation] AS do

JOIN    [OURDB].[dbo].[Donor] AS d ON do.[donorId] = d.[donorId]

WHERE   do.[donationDate] > '2015-07-01 00:00:00.000' and do.[donationDate] < '2016-07-01 00:00:00.000'

GROUP BY do.[donorId], d.[fn], d.[ln], do.[amount]

Upvotes: 0

Views: 94

Answers (1)

MikeS
MikeS

Reputation: 1764

Use the Having clause to do this:

SELECT  do.[donorId] as 'ID', 
        d.[fn] as 'First Name',
        d.[ln] as 'Last Name',
        SUM(do.[amount]) as Amount

FROM    [OURDB].[dbo].[Donation] AS do

JOIN    [OURDB].[dbo].[Donor] AS d ON do.[donorId] = d.[donorId]

WHERE   do.[donationDate] > '2015-07-01 00:00:00.000' and do.[donationDate] < '2016-07-01 00:00:00.000'

GROUP BY do.[donorId], d.[fn], d.[ln]
Having SUM(do.amount) > 4000

Upvotes: 3

Related Questions