Need to retrieve donor information AND total donations given for date range

[JohnDubya]

Hello all. I've come up on this issue that is baffling me. I have a query that grabs the donations given to us in a date range from our `chapter_donations` table, and it also grabs that donor's info from our `person` table. Here is a sample query that is run to grab all this info at once:


$x = mysql_query("SELECT *,
cd.donation_id AS cd_id,
DATE_FORMAT(cd.gift_date,'%M %D, %Y') AS date,
dc.name AS campaign,
cdf.name AS fund,
cdgt.name AS gift_type
FROM chapter_donations AS cd,
donor_campaigns AS dc,
donor_funds AS cdf,
donor_gift_types AS cdgt,
person AS p
WHERE cd.chapter_id = '3' AND cd.donor_id = p.person_id AND cd.campaign_id = dc.campaign_id
AND cd.fund_id = cdf.fund_id AND cd.gift_type_id = cdgt.gift_type_id
AND (cd.gift_date >= '2006-09-01' AND cd.gift_date <= '2007-08-31')
ORDER BY p.last_name ASC");


The issue I've run into is that our users want to be able to grab ONLY donors who have given a cumulative amount of x dollars for the date range. I tried doing "AND SUM(cd.amount) >= '2000'", but I got the "#1111 - Invalid use of group function" error from mysql. Is there any way of doing this in my situation?

[shyam]

aggregate comparisons like SUM(cd.amount) >= '2000' should appear in the having clause and not in the where clause

u can try adding
select
...
having SUM(cd.amount) >= '2000'
to ur existing query to see if it works in MySQL but usually to have a having clause u must have a group by clause and if u have a group by clause almost all selected columns except the aggregated column must appear in the group by clause...

[JohnDubya]

Hmm, mysql didn't give an error, but it only returned one row of a donor who had only given one donation of $300. That totally doesn't make sense from what the query is logically asking for. Or, more likely, I don't understand exactly what the query is asking for. Is there another way to do this through mysql? I'm not sure how to do GROUPing like what you described. Any guidance on that, if that's the route I need to go?

[Fumigator]

Assuming you are on a MySQL version that supports subqueries (I think 4.1+), then you can use an EXISTS in the WHERE clause:


AND EXISTS
(SELECT 1
FROM chapter_donations AS cd2
WHERE cd2.chapter_id = '3'
AND cd2.gift_date BETWEEN '2006-09-01' AND '2007-08-31'
GROUP BY cd2.chapter_id
HAVING SUM(cd2.amount) >= 2000)

[JohnDubya]

Thanks for the replies so far, guys.

Fumi, I changed the query to your suggestion, but it returned 261 rows, which there are nowhere near that many people who gave 2000 or more. Any other thoughts? Here's what the query looked like:

$x = mysql_query("SELECT *,
cd.donation_id AS cd_id,
DATE_FORMAT(cd.gift_date,'%M %D, %Y') AS date,
dc.name AS campaign,
cdf.name AS fund,
cdgt.name AS gift_type
FROM chapter_donations AS cd,
donor_campaigns AS dc,
donor_funds AS cdf,
donor_gift_types AS cdgt,
person AS p
WHERE cd.chapter_id = '3' AND cd.donor_id = p.person_id AND cd.campaign_id = dc.campaign_id
AND cd.fund_id = cdf.fund_id AND cd.gift_type_id = cdgt.gift_type_id
AND (cd.gift_date >= '2006-09-01' AND cd.gift_date <= '2007-08-31')
AND EXISTS
(SELECT 1
FROM chapter_donations AS cd2
WHERE cd2.chapter_id = '3'
AND cd2.gift_date BETWEEN '2006-09-01' AND '2007-08-31'
GROUP BY cd2.chapter_id
HAVING SUM(cd2.amount) >= 2000)
ORDER BY p.last_name ASC");

[Fumigator]

Hmm well when I'm figuring out queries this complex, I often break the subquery into its own query and run it ADHOC just to see what it's producing.

Looking at the subquery again, it doesn't appear to take the donor_id into consideration, and is probably showing results for every donor. Maybe if you link the subquery to the main query via donor_id you'll see better results...


SELECT 1
FROM chapter_donations AS cd2
WHERE cd2.chapter_id = '3'
AND cd2.gift_date BETWEEN '2006-09-01' AND '2007-08-31'
AND cd2.donor_id = cd.donor_id
GROUP BY cd2.chapter_id
HAVING SUM(cd2.amount) >= 2000


You'll probably end up linking to each of the tables you're joining with actually.

[JohnDubya]

That did it, thanks so much Fumi! Thanks comin.