need some help to build a query, this is my current scheme:
users:
+----+------------+
| id | username |
+----+------------+
| 1 | rob |
| 2 | john |
| 3 | jane | <--- jane never has donated
| 4 | mike |
+----+------------+
donations:
+--------------------+------------+
| uid | amount | date |
+---------+----------+------------+
| 1 | 20 | 2013-10-10 |
| 2 | 5 | 2013-10-03 |
| 2 | 50 | 2013-09-25 |
| 2 | 5 | 2013-10-01 |
| 4 | 100 | 2012-10-01 | <-- past year
+---------+----------+------------+
Result I want:
+---------+-------------+---------+-------------+---------------+----------+
| id | username | amount | monthly | totalamount | total |
+---------+-------------+---------+-------------+ --------------+----------+
| 1 | rob | 20 | 1 | 20 | 1 |
| 2 | john | 60 | 3 | 60 | 3 |
| 3 | jane | 0 | 0 | 0 | 0 |
| 4 | mike | 0 | 0 | 100 | 1 |
+---------+-------------+-----------------------+---------------+----------+
This is my query:
SELECT
u.*,
COALESCE(sum(d.amount), 0) amount,
COUNT(d.uid) monthly,
COUNT(d.amount) as Total, <-- need to get sum all time donations and number of times donated
FROM users u
LEFT JOIN donations d
ON u.id = d.uid
AND (month(d.date), year(d.date)) = (month(CURDATE()), year(CURDATE()))
GROUP BY u.id ORDER BY u.id ASC
So i need to add 2 different sums from same data.
EDIT: http://sqlfiddle.com/#!2/20a974/9 schema and data
How I can do this?
For this we need to filter the data on the select and not on the join.
Remove this condition:
AND (month(d.date), year(d.date)) = (month(CURDATE()), year(CURDATE()))
and add this to the select:
SUM (CASE WHEN (month(d.date), year(d.date)) = (month(CURDATE()), year(CURDATE())) THEN 1 ELSE 0 END) as monthly
Edit:
whole query:
SELECT users.id, users.username,
COALESCE(sum(CASE WHEN (month(donations.date), year(donations.date)) = (month(CURDATE()), year(CURDATE())) THEN donations.amount ELSE 0 END), 0) monthly_sum,
COALESCE(sum(CASE WHEN (month(donations.date), year(donations.date)) = (month(CURDATE()), year(CURDATE())) THEN 1 ELSE 0 END), 0) monthly_amount,
COALESCE(sum(donations.amount), 0) total_sum,
count(*) total_amount
from users
left join donations
on donations.uid = users.id
group by users.id, users.username
http://sqlfiddle.com/#!2/20a974/20/0
For me the easiest way to think about the separately grouped information is to put it into separate queries and then just join the results back together. This is not likely to be the most efficient, but it helps to get something working.
select auo.id, auo.username,
coalesce(monthly_count, 0), coalesce(monthly_total, 0),
coalesce(total, 0), coalesce(total_amount, 0)
from aaa_users auo
left join (
select au.id as id, count(adm.amount) as monthly_count, SUM(adm.amount) as monthly_total
from aaa_users au join aaa_donations adm on au.id = adm.uid and adm.donate_date > GETDATE()-30
group by au.id
) as monthly on monthly.id = auo.id
left join (
select au.id as id, count(ady.amount) total, SUM(ady.amount) as total_amount
from aaa_users au join aaa_donations ady on au.id = ady.uid and ady.donate_date > getDate()-450
group by au.id
) as yearly on yearly.id = auo.id
As @CompuChip said, it's cleaner to just join to the donations table twice, but I have something wrong in my join logic as the values for john are getting duplicated. I think there would need to be a donations.id column to prevent the monthly and total donations from being combined. Anyway, here's an example even though it isn't working correctly
select au.id, au.username,
count(adm.amount), SUM(adm.amount) as monthly_total,
count(ady.amount), SUM(ady.amount) as total_amount
from aaa_users au
left outer join aaa_donations adm on au.id = adm.uid and adm.donate_date > GETDATE()-60
left outer join aaa_donations ady on au.id = ady.uid and ady.donate_date > getDate()-450
group by au.id, au.username
order by au.id, au.username
You can do another join to donations, giving it a different alias: LEFT JOIN donations d2 on d2.uid = u.id. Then sum over d2.amount for the last two fields, e.g.
SELECT u.*,
COALESCE(sum(d.amount), 0) amount,
COUNT(d.uid) monthly,
COUNT(d.amount) as Total,
COALESCE(sum(d2.amount), 0) amountAll,
COUNT(d2.uid) monthlyAll,
COUNT(d2.amount) as TotalAll
FROM users u
LEFT JOIN donations d ON u.id = d.uid AND (month(d.date), year(d.date)) = (month(CURDATE()), year(CURDATE()))
LEFT JOIN donations d2 ON u.id = d2.uid
GROUP BY u.id ORDER BY u.id ASC
