Joining with subqueries, counting and grouping.

Question

Hello everyone,

I have 3 tables, which are each 1:n.

an entry in table1 has n entries in table2, and so on.

lets call them cars, wheels, and screws for illustration. Screws can be clean(1) or rusty(2)

I am joining them together, because I want to count 2 things.

First, I want to have rows telling me how many good/bad screws per wheel I have for each car. So basically I am getting:

car_id    wheel_id   screw_state  count(screws)
   1          1           1          3
   1          1           2          7
   1          2           1          5
   1          2           2          3
   2          1           1          1
                ... and so on...

Now I want a second fact, namely how many rusty and clean screws I have for all wheels per car, without needing to know each specific number per wheel. So basically now I just leave off the GROUP BY over wheel_id, like this:

car_id    screw_state  count(screws)
   1          1             8
   1          2            10
   2          1             1
                ... and so on...

The thing is, I would need both of them in one single query, because else I'd have a lot of sorting and rearranging to do. I believe the second, easier count over the total screws per car should be done as a subquery, but can I join the first, bigger query easily with a subquery?

How is this done? I would be happy over a quite specific answers, because I am not really an SQL wizard.

edit : I am working on an ORM, so funky thinks like below (hacking the col values to some constant) cant be done there easily. I have to get this solution working there, so JOIN/subquery/UNIONs without funky workarounds would be great.

Greetings, Tom

Answer 1

SELECT car_id, wheel_id, screw_state, count(screws)
  FROM cars C, wheels W, screws S
 WHERE W.car_id = C.car_id
   AND S.wheel_id = W.wheel_id
 GROUP BY car_id, wheel_id, screw_state
UNION ALL
SELECT car_id, -1 AS wheel_id, screw_state, count(screws)
  FROM cars C, wheels W, screws S
 WHERE W.car_id = C.car_id
   AND S.wheel_id = W.wheel_id
 GROUP BY car_id, screw_state
ORDER BY car_id

you can UNION 2 queries, the second one for all wheels per car, that's why wheel_id = -1.

result:

    car_id    wheel_id   screw_state  count(screws)
       1          1           1          3
       1          1           2          7
       1          2           1          5
       1          2           2          3
       1         -1           1          8
       1         -1           2         10
       2          1           1          1
       2         -1           1          1
...

Answer 2

A quick search says that MySQL supports GROUPING SETS. This is a good candidate for that feature:

SELECT car_id, wheel_id, screw_state, count(screws)
FROM cars C
JOIN wheels W ON W.car_id = C.car_id
JOIN screws S ON S.wheel_id = W.wheel_id
GROUP BY GROUPING SETS (
  (car_id, screw_state, wheel_id),
  (car_id, screw_state)
)
ORDER BY car_id, wheel_id, screw_state