mysql complex select query

Question

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.

What do you want to happen if you have two users in a division with the same assignment? Would that add 1 or 2 to the count? — jswolf19
– jswolf19, Commented Mar 22, 2011 at 12:23

BigFatBaby · Accepted Answer · 2011-03-22 12:27:49Z

3

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.

answered Mar 22, 2011 at 12:27

BigFatBaby

1,4989 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BigFatBaby Over a year ago

@Nurzhan : no problem at all - if your question was answered, make sure to check the correct answer to help other people who may encounter a similar issue

jswolf19 Over a year ago

@Nurzhan, I don't think this will add 2 to the count if two users in a division have the same assignment.

Collectives™ on Stack Overflow

mysql complex select query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related