How can i join sql subqueries as they are?-CodePudding

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

That unfortunately is not the given result.

I hope this makes sense to a certain extent.

CodePudding user response：

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)