mysql count join

Question

SELECT A.id as ActivityId, A.description, T.id, T.title, COUNT(R.*) as reactionCount
FROM activities 
LEFT JOIN activitiesReactions as R ON R.activityId = A.id
LEFT JOIN activitiesTags as T ON A.tagId = T.id

So basically I need a query that will get all the activities, and at the same time NOT get the reactions of that activity but get the COUNT of reactions, which are found in another table called activitiesReactions, how do I do this (see above query that I had in mind).

So the query should return:

array('activityId' => 3, 'description' => 'doing work', 'reactionCount' => 2)

An example row:

Activities table:
id | description
 3   doing work
 4   checking mail

ActivitiesReactions table:
id | activityId | message
 1            3   you never do anywork, so that must be bullshit.
 2            3   yes I do alot of work!

so now it should return, "2" on reactionCount when I execute the query and doing WHERE A.id = 3

SELECT A.id as ActivityId, A.description, COUNT(R.activityId) AS reactionCount
FROM activities 
LEFT JOIN activitiesReactions as R
ON R.activityId = A.id
GROUP BY A.id

That did work, but the reactionCount returns as * 2, so for example if there are 3 reactions the reactionCount = 6, 2 reactions the reactionCount = 4 etc.

Your example does not make any sense … How do you come up with 2 as a result? Also, your array does not match the rows you show us — knittl
– knittl, Commented Nov 28, 2011 at 13:16

a'r · Accepted Answer · 2011-11-28 13:08:49Z

5

Your query only needs a group by clause to make it work, eg.

SELECT A.id as ActivityId, A.description, COUNT(R.*) 
FROM activities 
    LEFT JOIN activitiesReactions as R ON R.activityId = A.id
GROUP BY A.id, A.description;

answered Nov 28, 2011 at 13:08

a'r

37.2k7 gold badges68 silver badges67 bronze badges

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

1 Comment

ypercubeᵀᴹ Over a year ago

You don't need the A.description in the group by in MySQL. You only need: GROUP BY A.id

knittl · Accepted Answer · 2011-11-28 13:19:56Z

2

If you want to count the number of reactions per activity, you have to group by activity.id after joining both tables:

SELECT A.id as ActivityId, A.description, COUNT(*) AS reactionCount
FROM activities 
LEFT JOIN activitiesReactions as R
ON R.activityId = A.id
GROUP BY A.id

edited Nov 28, 2011 at 13:19

answered Nov 28, 2011 at 13:09

knittl

269k59 gold badges339 silver badges405 bronze badges

5 Comments

user1066101 Over a year ago

Oke the reactionCount should be only COUNT(R.*) which is giving me an error, is it impossible? The problem is that I'm LEFT joining other tables too. (fixed my example)

user1066101 Over a year ago

Oke that worked, but for some reason the count is 2*the actual count, any clue? So if there are 2 reactions, the reactionCount = 4, if there are 3 reactions the reactionCount = 6

knittl Over a year ago

@user1066101, @ypercube: It should not matter if you group by * or by the id column of R (id will never be null due to the nature of join, so it does not make any difference). @user1066101: If you additionally join tags, you will always get COUNT(*) times number_of_tags_per_activity. I would not join the tag table if you don't have very good reasons for doing so …

user1066101 Over a year ago

I just have to get the tag of the activity too, shall I put it in a different query than?

knittl Over a year ago

I think your DB schema is flawed (or your example is not correct again) … the way you are joining the tables now, there can only be a single tag per activity (and then it does not explain why you are getting multiple rows …). But yes, you should query the tags in a different query, because otherwise you will not get the result you desire … (don't attempt to do multiple things in a single just because you can – use the right queries for the right job)

Collectives™ on Stack Overflow

mysql count join

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related