CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Tom
Tom

Reputation: 3183

Joining with subqueries, counting and grouping

I have three tables, which are each 1:n. An entry in table1 has n entries in table2, and so on. Let's 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 two 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) can't be done there easily. I have to get this solution working there, so JOIN/subquery/UNIONs without funky workarounds would be great.

Upvotes: 0

Views: 250

Answers (2)

Steve Kass
Steve Kass

Reputation: 7184

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

Upvotes: 1

manji
manji

Reputation: 47978

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
...

Upvotes: 1

Related Questions