[SOLVED] Query help

[Brian W]

Working on a query that will group the total amount that a donor has donated with the donor name.

Example query:

SELECT
d.Pub_Name as Pub_Name,
f.Amount as Amount, f.Date as Date, f.ID as ID, f.contact_id as contact_id
FROM donors d
LEFT JOIN donations f
ON f.contact_id = d.contact_id
WHERE
Amount >= 0
AND Amount <= 9999999
ORDER BY Date DESC

I want amount per donor to be summed so I can show the total of all donations given in a time period.

I'm thinking I may need to reverse who is on the left of the left-join, but still don't know how to get the sum of all donations associated with that donor without placing a query for donations in my loop of donors.

 

Appreciate any input. Thanks

[Maq]

Why can't you use the aggregate SUM function on amount?

[Brian W]

Well, just as I got the notify someone had replied to my topic, I found the solution.

SELECT
d.Pub_Name as Pub_Name,
sum(f.Amount) as Amount, f.Date as Date, f.ID as ID, f.contact_id as contact_id
FROM donations f
LEFT JOIN donors d
ON d.contact_id = f.contact_id
WHERE
Amount >= 0
AND Amount <= 9999999
GROUP BY d.contact_id
ORDER BY Date DESC

A combination of sum and grouping.

Thanks Maq for the reply though. I had already tried sum and wasn't getting the results I was hoping for, but now I am with group.