I had always face a scenerio like this:

There are 2 tables

1
2
user(id, first_name, last_name)
transaction(id, qty, user_id)

Now I want to SUM all the quantity based on user, typically what I did is

1
2
3
4
SELECT user_id, SUM(qty) as total
FROM transaction
GROUP BY user_id
ORDER BY total DESC

But the problem is, I want the first_name and last_name as well. How to get this in one query?

I had found a solution here:

1
2
3
4
5
6
7
8
SELECT id, first_name, last_name, total
FROM user u
JOIN (
  SELECT user_id, SUM(qty) as total
  FROM transaction
  GROUP BY user_id
) AS t ON u.id = t.user_id
ORDER BY total DESC