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
x
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)