MySQL query counting on multiple tables

Question

At the moment, we have 3 queries. In php, we loop over the first, then execute the 2nd multiple times, then which I'd like to have in one single query:

The first query is:

SELECT id FROM users

Then inside looping over those results, the 2nd is

SELECT id AS rid, count(recommendedById) FROM users WHERE id=$id

where $id is users.id from the first query.

The 3rd query is which is executed inside the 2nd loop is:

SELECT count(likes) AS likeCounter FROM posts WHERE author_id=$rid

and likeCounter is summed up to the first query.

Anyone able to bring this into one query?

Desired result The result should be a row per user with a count of users he recommended and a sum of likes his recommended users got on their posts.

What is the final result? One value that is the sum? Or a separate count for each user? — Gordon Linoff
– Gordon Linoff, Commented Sep 16, 2013 at 16:23
The result should be a row per user with a count of users he recommended and a sum of likes his recommended users got on their posts. I'll add that above. — Raphael Jeger
– Raphael Jeger, Commented Sep 16, 2013 at 16:24

cske · Accepted Answer · 2013-09-16 17:17:04Z

1

SELECT u.id,COUNT(DISTINCT ruid),sum(p.likes)
FROM users as u
LEFT JOIN (SELECT recommendedById as rid,id as ruid from users) as r ON r.rid = u.id
LEFT JOIN posts p ON p.author_id = ruid
GROUP BY u.id

edited Sep 16, 2013 at 17:17

answered Sep 16, 2013 at 16:33

cske

2,2334 gold badges27 silver badges24 bronze badges

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

12 Comments

cske Over a year ago

you shoud include the users table structure into the question, i was forced to guess it

cske Over a year ago

I added an another query, that may have a nicer query plan

Havenard Over a year ago

In this fiddle author 2 has a post in which he received 30 likes, and author 3 has a post in which he received 1 like, but your query don't show any of this. Instead SUM(p.likes) is accumulating 31 to author 1 that should have 20 likes.

cske Over a year ago

counting the likes of reccomended users, 1 recommended 2,3 so those likes counts there, 3 recommended none, 2 recommended 4,5 but no post for that users

Havenard Over a year ago

sum of likes his recommended users got on their posts oh okay I missed this part... kinda weird statistic

|

Niels · Accepted Answer · 2013-09-16 16:31:31Z

1

You can do this:

SELECT u.id AS rid, count(recs.id), count(p.likes) AS likeCounter
FROM users u 
LEFT JOIN posts p ON p.author_id=u.id
LEFT JOIN users recs ON recs.recommendedById=u.id
GROUP BY u.id

But a user has an id, and you use id from the users table. Isn't that always 1?

edited Sep 16, 2013 at 16:31

answered Sep 16, 2013 at 16:25

Niels

50.1k4 gold badges68 silver badges86 bronze badges

4 Comments

Raphael Jeger Over a year ago

what is $id? I'd like to have it as a pure sql-query. Thanks so far!

Niels Over a year ago

Now its for all users, just removed the WHERE statement

Raphael Jeger Over a year ago

It doesn't work like it should. Likes should be counted on users you recommended, so if your users.id is "32", and users.id 63/67/71 have users.recommendedByUserId "32", the likes on their posts should be summed up and shown on users.id 32

Niels Over a year ago

So more like I got now? A count of recommended users.

Collectives™ on Stack Overflow

MySQL query counting on multiple tables

2 Answers 2

12 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related