Using SQL JOIN and COUNT

Question

Let there be two tables, one holding user information and one holding user records of some sort, say receipts. There is a one-to-many relationship between the users and receipts.

What would be the best SQL method of retrieving users, sorted by the greatest number of receipts?

The best way I can think of is using a join and count(?) to return an array of users and their number of associated receipts.

Is there a way to make use of the count function in this instance?

select * from `users` inner join `receipts` on `users`.`id` = `receipts`.`uId`

Μenelaοs · Accepted Answer · 2013-06-03 15:46:07Z

6

If OP wishes to include additional information (additional aggregations, etc...) utilizing data from users table:

SELECT `users`.`id`,
       count(`receipts`.`uId`)
FROM `users`
INNER JOIN `receipts` ON `users`.`id` = `receipts`.`uId`
GROUP BY `users`.`id`
ORDER BY count(`receipts`.`uId`) DESC

Otherwise, only the receipts table is required...

SELECT `users`.`id`,
       count(`receipts`.`uId`)
FROM `receipts`
GROUP BY `receipts`.`uId`
ORDER BY count(`receipts`.`uId`) DESC

edited Jun 3, 2013 at 15:46

answered Jun 3, 2013 at 14:52

Μenelaοs

24.4k20 gold badges98 silver badges166 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gordon Linoff Over a year ago

. . If you are going to limit the return to only the id, the join is not necessary.

Gordon Linoff Over a year ago

. . This query is still wrong, unless their happens to be a column called receipts in the receipts table.

IanB · Accepted Answer · 2016-04-11 03:15:33Z

4

Two answers provided by Dave and meewoK will accomplish what you need. I'm providing an alternative, which should provide better performance and allow you to show more user information because in the case with Dave's answer you can only SELECT columns that are used by an aggregate function or in the group clause.

SELECT users.id, users.name, r.numReceipts  
FROM users u
INNER JOIN (
   SELECT uId, count(receipts) as numReceipts
   FROM receipts
   GROUP BY receipts.id
) as r ON r.uId = u.id
ORDER BY r.numReceipts DESC

This creates an inline view. Only return the count of receipts of each user and then join this inline view on the user's ID.

Some one correct me if I'm wrong, but I've been told that the planner isn't as efficient when you do a scalar subquery in the SELECT clause. It's better to join on a temporary table this way. There are multiple ways to write this query and it all depends on how you want to use the information!!! Cheers!

edited Apr 11, 2016 at 3:15

IanB

2,69423 silver badges28 bronze badges

answered Jun 3, 2013 at 15:16

Tim Sanders

8713 gold badges17 silver badges26 bronze badges

2 Comments

Dave Over a year ago

tbh I have no idea which is faster I suspect my way of doing it was considerably slower (certainly on larger tables) as your doing a full table b scan and count for each uid from table a. My answer also depends on him not wanting any data from the receipts table where as yours gives him that :) though tbh a sub select in a join probably isn't the best way of doing it either. I'd be tempted to do an outer left join if I wanted a counter and retrieve the details of both tables. Or perhaps a union and just select the whole lot :)

Tim Sanders Over a year ago

Yes, I definitely agree a left join is the most efficient way of handling this task.

Fabio · Accepted Answer · 2013-06-03 14:52:49Z

3

try this

SELECT a.`id`, count(b.`recipts`) as total_receipts
FROM `users` a
INNER JOIN `receipts` b
ON a.`id` = b.`uId`
GROUP BY a.`id` 
ORDER BY count(b.`receipts`) desc

answered Jun 3, 2013 at 14:52

Fabio

23.5k12 gold badges57 silver badges66 bronze badges

Comments

Dave · Accepted Answer · 2013-06-03 14:53:38Z

2

SELECT users.*, (SELECT COUNT(*) FROM tblreceipts WHERE tblreciepts.uId=users.id) as counter FROMusersORDER BY counter DESC

Something like this may work (not sure on the speed though if its big tables)

answered Jun 3, 2013 at 14:53

Dave

3,2882 gold badges25 silver badges40 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2013-06-03 15:01:18Z

2

If you want to include all users, even those with no receipts, then a good way is a left outer join:

SELECT u.*, count(r.uid) as NumReceipts
FROM `users` u left outer join
     `receipts` r
    ON u.id = r.`uId
GROUP BY `u.id
ORDER BY NumReceipts DESC;

If you only want the id for users that have receipts, then the join is not even necessary:

SELECT r.uid, count(*) as NumReceipts
FROM receipts r
GROUP BY r.uid
ORDER BY NumReceipts

answered Jun 3, 2013 at 15:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Using SQL JOIN and COUNT

5 Answers 5

2 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related