Skip to content
Advertisement

How can I join SQL subqueries as they are?

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

Standalone

This is the result

Snippet above being run

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)
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement