Mysql - Multiple groups query

Question

I need a little help here It is very simple problem but I don't know why I can't get what I want

Database

store
id | value
5  |   s

favorite
id | store_id | value
1  |    5     |   f
2  |    5     |   f 
3  |    5     |   f

party
id  |store_id | value
1   |    5    |  p
2   |    5    |  p

my query :

SELECT COUNT(p.`id`) AS parties, COUNT(f.`id`) AS favorites,s.* FROM store s
LEFT JOIN party p ON p.`store_id` = s.`id`
LEFT JOIN favorite f ON f.`store_id` = s.`id`
GROUP BY s.`id`

result

parties | favorites |   id   | value
   6    |     6     |    5   |   s

this is the result I want

parties | favorites |   id   | value
   2    |     3     |    5   |   s

Can anyone help me?, I already tried all approach I can think of

Joachim Isaksson · Accepted Answer · 2016-01-13 09:16:57Z

1

You need to count DISTINCT id's if you want a separate count of parties/favorites per store, that will eliminate the duplicates you're getting due to the JOIN;

SELECT COUNT(DISTINCT p.`id`) AS parties, 
       COUNT(DISTINCT f.`id`) AS favorites,
       s.* 
FROM store s 
LEFT JOIN party p ON p.`store_id` = s.`id` 
LEFT JOIN favorite f ON f.`store_id` = s.`id` 
GROUP BY s.`id`;

answered Jan 13, 2016 at 9:16

Joachim Isaksson

182k28 gold badges298 silver badges308 bronze badges

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

1 Comment

Eleojasmil J Milagrosa Over a year ago

Thanks, it really answer my question

Collectives™ on Stack Overflow

Mysql - Multiple groups query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related