looking for some solutions? You are welcome.

SOLVED: How to COUNT and SELECT with WHERE in sql?

darkermuffin:

I have two tables user and group and also a usergroup as a link table which contains which users exist in a group.

user table

+-----+-------+
| id  | name  |
+-----+-------+
| U12 | John  |
| U13 | Rick  |
| U14 | Morty |
| U15 | Alex  |
| U16 | Felix |
+-----+-------+

group table

+-----+--------+--------+
| id  |  name  | points |
+-----+--------+--------+
| G12 | Red    |     11 |
| G13 | Blue   |     22 |
| G14 | Green  |     55 |
| G15 | Yellow |     64 |
| G16 | Orange |     23 |
+-----+--------+--------+

usergroup table

+----+---------+----------+
| id | user_id | group_id |
+----+---------+----------+
|  1 | U12     | G12      |
|  2 | U14     | G12      |
|  3 | U15     | G12      |
|  4 | U15     | G15      |
|  5 | U12     | G13      |
+----+---------+----------+

To select the groups that a particular user is in, I can just do

SELECT group.*
FROM usergroup 
INNER JOIN group 
ON group.id = usergroup.group_id 
WHERE usergroup.user_id = ?

But how to also simultaneously select the number of total users in the same group?

Expected Sample Output - for the groups that user U12 is in, along with total user count

+-----+------+--------+-------------+
| id  | name | points | users_count |
+-----+------+--------+-------------+
| G12 | Red  |     11 |           3 |
| G13 | Blue |     22 |           1 |
+-----+------+--------+-------------+



Posted in S.E.F
via StackOverflow & StackExchange Atomic Web Robots
Share:

No comments:

Recent