Sum Two Rows And Order By Date / Total

need some help to build a query, this is my current scheme: users: +----+------------+ | id | username | +----+------------+ | 1 | rob | | 2 | john | | 3 | jan

Solution 1:

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 (CASEWHEN (month(d.date), year(d.date)) = (month(CURDATE()), year(CURDATE())) THEN1ELSE0END) as monthly

Edit:

whole query:

SELECT users.id, users.username, 
  COALESCE(sum(CASEWHEN (month(donations.date), year(donations.date)) = (month(CURDATE()), year(CURDATE())) THEN donations.amount ELSE0END), 0) monthly_sum, 
  COALESCE(sum(CASEWHEN (month(donations.date), year(donations.date)) = (month(CURDATE()), year(CURDATE())) THEN1ELSE0END), 0) monthly_amount, 

  COALESCE(sum(donations.amount), 0) total_sum, 
  count(*) total_amount

from users
leftjoin donations
  on donations.uid = users.id
groupby users.id, users.username

http://sqlfiddle.com/#!2/20a974/20/0

Solution 2:

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()-30groupby 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()-450groupby 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 
leftouterjoin aaa_donations adm on au.id = adm.uid and adm.donate_date > GETDATE()-60leftouterjoin aaa_donations ady on au.id = ady.uid and ady.donate_date > getDate()-450groupby au.id, au.username
orderby au.id, au.username

Solution 3:

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
     LEFTJOIN donations d ON u.id = d.uid AND (month(d.date), year(d.date)) = (month(CURDATE()), year(CURDATE()))
     LEFTJOIN donations d2 ON u.id = d2.uid 

 GROUPBY u.id ORDERBY u.id ASC