StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POJoining with subqueries, counting and grouping
text
Body
copied!<p>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:</p> <pre><code>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... </code></pre> <p>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:</p> <pre><code>car_id screw_state count(screws) 1 1 8 1 2 10 2 1 1 ... and so on... </code></pre> <p>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. </p> <p>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?</p> <p>How is this done? </p> <p>I would be happy over a quite specific answers, because I am not really an SQL wizard.</p> <p><em>edit</em> : 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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload