CODEWITHSUNDEEP
phpmysqlsqldatabase
GVillani82
GVillani82

Reputation: 17429

Issue creating query in mysql

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                             |

Upvotes: 1

Views: 66

Answers (3)

Mihai
Mihai

Reputation: 26784

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

Upvotes: 2

Rajeev Ranjan
Rajeev Ranjan

Reputation: 4142

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270503

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.

Upvotes: 4

Related Questions