CODEWITHSUNDEEP
sqlsql-serverjoinsum
JDV72
JDV72

Reputation: 29

SQL - Restricting JOINed Records by Date

Using two tables in MSSQL:

One table, [CUSTOMER], containing information on donors. - Relevent columns: [CustID], [Name] One table, [DONATION], containing records for each donation given. - Relevent columns: [CustID], [Amount], [Date] The tables share a key, [CustID].

I want to aggregate the [Amounts] according to each [CustID]

SELECT DONATION.CUSTID
,PEOPLE.NAME
,SUM (DONATION.AMOUNT) as TOTAL_DONATION
FROM [dbo].[DONATION] INNER JOIN [dbo].[PEOPLE] ON DONATION.CUSTID = PEOPLE.CUSTID
GROUP BY
DONATION.CUSTID
,PEOPLE.NAME
HAVING SUM (DONATION.AMOUNT) > 100

This query works fine, even with adding the HAVING clause.

When I want to restrict the dates of donations to aggregate (adding to the SELECT,GROUP BY, and HAVING clauses) however...

SELECT DONATION.CUSTID
,PEOPLE.NAME,DONATION.DATE
,SUM (DONATION.AMOUNT) as TOTAL_DONATION
    FROM [dbo].[DONATION] INNER JOIN [dbo].[PEOPLE] ON DONATION.CUSTID = PEOPLE.CUSTID
    GROUP BY
DONATION.CUSTID
,PEOPLE.NAME
,DONATION.DATE
HAVING SUM (DONATION.AMOUNT) > 100
and DONATION.DATE > '1-1-2010'

The query no longer returns aggregate sums of each person's donations, but returns individual donations for each person, which meet the HAVING criteria.

How can I implement this date restriction? Is it how I'm joining or summing or....? Thanks.

Upvotes: 0

Views: 484

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

Move it to the WHERE clause

SELECT DONATION.CUSTID
      ,PEOPLE.NAME
      ,SUM (DONATION.AMOUNT) as TOTAL_DONATION
FROM [dbo].[DONATION]
INNER JOIN [dbo].[PEOPLE] ON DONATION.CUSTID = PEOPLE.CUSTID
WHERE DONATION.DATE > '1-1-2010'
GROUP BY
       DONATION.CUSTID
      ,PEOPLE.NAME
HAVING SUM (DONATION.AMOUNT) > 100

What this means:

- given the people who donated
- look only at data where donation date is in 2010 or later
- and within that data, show the people who donated a total of more than 100

Upvotes: 5

Jerry Ritcey
Jerry Ritcey

Reputation: 311

You can't group by date unless you want one row per customer per date. How about doing it from the donation table and just looking up customer names?

select Donator=(select name from [people] where [people].custid=[donation].custid), custid,SUM(amount) from [donation] where [donation].date between '1/1/2011' and '1/15/2011' group by custid having SUM(amount)>100

Upvotes: 0

Andomar
Andomar

Reputation: 238076

Modify your first query like:

,SUM (CASE WHEN DONATION.DATE > '1-1-2010' THEN DONATION.AMOUNT END) 
     as TOTAL_DONATION

Upvotes: 0

Related Questions