Generate two columns from three different tables and calculate the ratio of those columns using a gr-CodePudding

I am trying to get two counts of the occurence of an event with two different WHERE clauses. I would like the end result columns to be displayed side by side. Lastly, for each count, I need to obtain a display name column from another table. I would like to do all this in a query.

EDIT Changed the grouping variable to table1.column1 (it's equivalent since it is the name rather than the id, but both should be unique.

SELECT table1.column1, COUNT(DISTINCT table2.column1) AS newcolumn1
FROM table2
LEFT JOIN table1 ON (table1.column2 = table2.column2)
WHERE table2.column3 != 'var1'
GROUP BY table1.column1
UNION ALL
SELECT table1.column1, COUNT(DISTINCT table2.column1) AS newcolumn2
FROM table2
LEFT JOIN table1 ON (table1.column2 = table2.column2)
WHERE table2.column3 = 'var2'
GROUP BY table1.column1

This sort of does what I want, but it stacks the columns instead of displaying them side by side. The end game is to have a column that is the ratio of newcolumn 1 and newcolumn 2. Unfortunately, this is a far as I managed to get.

CodePudding user response：

You could try using separated left join instead of union

    select table1.column1, tvar1.newcolumn1, tvar2.newcolumn2 
    from table1 
    left join (
        SELECT table1.column1, COUNT(DISTINCT table2.column1) AS newcolumn1
        FROM table2
        LEFT JOIN table1 ON (table1.column2 = table2.column2)
        WHERE table2.column3 = 'var1'
        GROUP BY table1.column1
    ) tvar1 on tvar1.column1 = table1.column1
    left join  (

        SELECT table1.column1, COUNT(DISTINCT table2.column1) AS newcolumn2
        FROM table2
        LEFT JOIN table1 ON (table1.column2 = table2.column2)
        WHERE table2.column3 = 'var2'
        GROUP BY table1.column1
    ) tvar2 on tvar2.column1 = table1.column1