I have 3 subqueries that when executed independently they all return 3 rows with the desired columns and values. Once I put them all in the from statement and select them all
SELECT *, ROUND(Verbrecher / Buerger * 100, 1) AS Sicherheitsgrad FROM (SELECT name AS Dorf FROM dorf GROUP BY dorfnr) AS Dorf, (SELECT COUNT(*) AS Verbrecher FROM bewohner WHERE status LIKE 'boese' GROUP BY dorfnr) AS Verbrecher, (SELECT COUNT(*) AS Buerger FROM bewohner GROUP BY dorfnr) AS Buerger
This is the result of all three subqueries being respectively executed standalone
This is the result
I expect them to be joined together and have three rows with the queries aligned horizontally.
That unfortunately is not the given result.
I hope this makes sense to a certain extent.
Advertisement
Answer
Maybe you need in this:
SELECT dorfnr, Dorf, Verbrecher, Buerger, ROUND(Verbrecher / Buerger * 100, 1) AS Sicherheitsgrad FROM ( SELECT dorfnr, name AS Dorf FROM dorf -- GROUP BY dorfnr ) AS Dorf JOIN ( SELECT dorfnr, COUNT(*) AS Verbrecher FROM bewohner WHERE status LIKE 'boese' GROUP BY dorfnr ) AS Verbrecher USING (dorfnr) JOIN ( SELECT dorfnr, COUNT(*) AS Buerger FROM bewohner GROUP BY dorfnr ) AS Buerger USING (dorfnr)