CODEWITHSUNDEEP
mysqlsqljoincount
user1066101
user1066101

Reputation: 65

mysql count join

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.

Upvotes: 0

Views: 7615

Answers (2)

knittl
knittl

Reputation: 265956

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

Upvotes: 2

a'r
a'r

Reputation: 37029

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;

Upvotes: 5

Related Questions