I have two tables, in mySql database, like these :
GROUPS:
| IDGroup| IDRat| Type| Category|
| 708 | 354 | 1 | A |
| 709 | 354 | 1 | C |
Where IDGroup is the primary key.
USERS:
| IDUser| Name| Surname | IDGroup |
| 1 | John| Doe | 708 |
| 2 | Mick| Caine | 708 |
Where the primary key is IDUser
I want create a query that, receives as parameter the IDRat and returns the results like this:
| IDGroup| Type| Category | Number_of_Users_in_this_group |
where, as the name suggest, the fourth column represents the number of users associated with the group.
For example passing the IDRat = 354 I want obtain the following result:
| IDGroup| Type| Category | Number_of_Users_in_this_group |
| 708 | 1 | A | 2 |
| 709 | 1 | C | 0 |
The following query does what you want:
SELECT g.IDGroup, g.Type, g.Category, COUNT(u.IDGroup)
FROM GROUPS g left join
USERS u
on g.IDGroup = u.IDGroup
WHERE IDRat = XXX
GROUP BY g.IDGroup;
The left join keeps all groups. The count() will return 0 when there is no match. The where clause does the filtering.
SELECT IDGroup,Type,Category,COUNT(u.IDGroup) as NoUsers
FROM GROUPS g
LEFT JOIN USERS u ON g.IDGroup=u.IDGroup
WHERE IDRat=354 GROUP BY g.IDGroup
select required column and join these table and group result by IDGroup
select IDGroup,Type,Category,COUNT(IDGroup) as Number_of_Users_in_this_group
FROM GROUPS JOIN USERS WHERE GROUPS .IDGroup=USERS .IDGroup
HAVING IDRat=354 GROUP BY IDGroup