CODEWITHSUNDEEP
mysqlselectcount
Nurjan
Nurjan

Reputation: 6053

mysql complex select query

I have 4 tables:

user,
administrative_division,
assignment,
user_has_assignment

Between user and administrative_division there is a many-to-one relationship where many is user and one is administrative_division. Between user and assignment there is a many-to-many relationship through the user_has_assignment table.

I can get how many users are in each administrative_division by using this command:

select division_id, count(*) from user group by division_id;

Which, for example, gives me something like this:

division_id count(*)
  1           4
  2           10

etc.

I can get how many assignments are owned by each user by using this command:

select user_id, count(*) from user_has_assignment group by user_id;

which gives similar result

user_id count(*)
   1       2
   2       10

But I don't know how I can get in one query a result like this:

division_id  user_count assignment_count
     1           10            20 
     2            2            4

where user_count is the total number of users in each administrative_division and assignment_count is the total of all assignments owned by users from one particular administrative_division.

Upvotes: 0

Views: 563

Answers (1)

BigFatBaby
BigFatBaby

Reputation: 1510

you simply need to join the tables above and group the results by the devision_id, so you would get a single row per devision like so:

SELECT ad.devision_id,
       COUNT(DISTINCT u.user_id) AS user_count,
       COUNT(DISTINCT a.assignment_id) AS assignment_count
FROM user u
     JOIN administrative_division ad ON u.devision_id = ad.devision_id
     LEFT JOIN user_has_assignment ucon ON u.user_id = ucon.user_id
     LEFT JOIN assignment a ON a.assignment_id = ucon.assignment_id

check the field names in the query and replace them accordingly in order for the query to work on your schema.

Upvotes: 3

Related Questions