-3

I have two tables with some specific data:

users:
+----+------------+
| id |  username  |
+----+------------+
| 1  |    rob     |
| 2  |    john    |
| 3  |    jane    | <--- jane never has donated
+----+------------+

donations:
+--------------------+------------+
|   uid   |  amount  |   date     |
+---------+----------+------------+
|    1    |    20    | 2013-10-10 | 
|    2    |    5     | 2013-10-03 | 
|    2    |    50    | 2013-09-25 |
|    2    |    5     | 2013-10-01 |
+---------+----------+------------+


Result I want:
+---------+-------------+---------+-------------+
|    id   |   username  | amount  |   monthly   | <- sum of donations this month
+---------+-------------+---------+-------------+
|    1    |     rob     |   20    |      1      |
|    2    |     john    |   60    |      3      |
|    3    |     jane    |   0     |      0      | <- jane added
+---------+-------------+-----------------------+

This is my query:

SELECT t1.*, sum(t2.amount) amount, count(*) as monthly
FROM users t1
inner join donations t2
  on t2.uid = t1.id
group by t1.username

EDIT: forgot to add jane, he never has donated.

How I can do this?

greenbandit
  • 2,267
  • 5
  • 30
  • 44

3 Answers3

1

Your output is wrong as you didn't filter out the september record in your results (only October 2013 should be taken into account).

Your expected output should be this:

| ID | USERNAME | AMOUNT | MONTHLY |
|----|----------|--------|---------|
|  1 |      rob |     20 |       1 |
|  2 |     john |     10 |       2 |
|  3 |     jane |      0 |       0 |

The query to get the output is:

SELECT
  u.id,
  u.username,
  COALESCE(sum(d.amount), 0) amount,
  COUNT(d.uid) monthly
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

Assuming users.ID holds unique values you can leave the group by as u.id if it is not then you will have to group by u.id, u.username.

Fiddle here.

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
  • amazing! it works,is possible to add the sum of all donations? from all time? – greenbandit Oct 25 '13 at 21:28
  • My advice would be to solve that in a separate query. Right now you're grouping by username. If you add a new column with that information it would contain the same number repeated in all rows (not nice). Oh, you mean PER USER? – Mosty Mostacho Oct 25 '13 at 21:31
  • in current query we selected from current month right? is also possible to add the same but from all time – greenbandit Oct 25 '13 at 21:32
  • It is possible but that radically changes the question and the answer, and I have to leave now. I'd advise you to create a new question providing the expected output and someone out there will answer it :) – Mosty Mostacho Oct 25 '13 at 21:38
  • hi again, please help me again with this: http://stackoverflow.com/questions/19600003/sum-two-rows-and-order-by-date-total , I've added a fiddle – greenbandit Oct 25 '13 at 22:42
0
SELECT t1.*, sum(t2.amount) as amount, count(t2.amount,t2.`date`) as monthly
FROM users t1
inner join donations t2
  on t2.uid = t1.id
where month(t2.`date`)=month(curdate()) and year(t2.`date`)=year(curdate())
group by t1.username
James
  • 4,211
  • 1
  • 18
  • 34
  • 2
    If you group that way you assume that the `username` field functionally determines any other field in the `t1` table. Not advisable at all. – Mosty Mostacho Oct 25 '13 at 20:50
0

How about this use current date and extract the month and match with the month from column date

CURDATE() , MONTH()

SELECT t1.*, (CASE WHEN SUM(t2.amount) IS NULL THEN 0 ELSE SUM(t2.amount) END) amount,
count(*) as monthly
FROM users t1
left join donations t2
  on t2.uid = t1.id
WHERE  MONTH(t2.`date`)=MONTH(CURDATE()) AND  YEAR(t2.`date`)=YEAR(CURDATE())
group by t1.id
M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118