CODEWITHSUNDEEP
phpmysqljoincountsql-order-by
Tim Lind
Tim Lind

Reputation: 160

SQL: How to Count occurrences in TableA, join with TableB and then order by occurences?

What I want:

For every User that is connected to Group_ID=1 I want to count the amount of activities this User have attended, and use that info to order by User with the most attendings DESC(ending).

A table that connects Users to Groups:

user_r_group:

ID    User_ID    Group_ID

1     1          1
2     3          1
3     2          1
4     1          2  <-- User 1 connected to another group

A table that shows which activities User have attended:

activities_attended:

ID    Activity_ID    User_ID

1     1              1  <-- User 1 have attended 3 activities
2     1              3
3     1              2
4     2              1  <-- User 1 have attended 3 activities
5     2              3
6     3              1  <-- User 1 have attended 3 activities

The outcome I'm looking for:

User_ID    Attendings

1          3
3          2
2          1

Is there a way to write a mysql_query to achieve this?

It sure feels like it. Or will I need to puzzle myself with loops in php with querycombinations?

I've checked out ways to join tables, and I'm slightly familiar with ORDER BY, but I have no real clue how to store the attendings-info for every User and then order by it, to then put it all together in a query as wanted ;/ A lil help please?

Solved it!

Thanks to GordonM for guidance on where to look, and thanks to Milen Pavlov as well as Kirill Fuchs for the code-samples. I combined the codes into my own prefered version. Kirill's code was however accurate on exception that he forgot the 'ORDER BY Attendings DESC' in the end ;)

SELECT
    COUNT(aa.User_ID) as Attendings,
    urg.User_ID
FROM
    activities_attended aa
    INNER JOIN user_r_group urg ON urg.User_ID=aa.User_ID
WHERE
    urg.Group_ID=1
GROUP BY
    urg.User_ID
ORDER BY
    Attendings DESC

Upvotes: 3

Views: 166

Answers (1)

Kirill Fuchs
Kirill Fuchs

Reputation: 13696

You will have to use a join. Example below:

SELECT `user_r_group`.`User_ID`, count(`activities_attended`.`ID`) AS Attendings
            FROM `user_r_group`
            INNER JOIN `activities_attended`
                    ON `user_r_group`.`User_ID` = `activities_attended`.`User_ID`
            WHERE `user_r_group`.`Group_ID` = 1
                    GROUP BY `user_r_group`.`User_ID`
                    ORDER BY Attendings DESC

Upvotes: 3

Related Questions