Total of group by divided by total of group by after where statement-CodePudding

I get the total number after grouping by and the total of the filled rows after grouping by.

SELECT X, COUNT(*) as total
FROM table 
WHERE Y IS NULL 
GROUP BY X

This for example results in row 1 has 1000 values

SELECT X, COUNT(*) as total
FROM table
GROUP BY X

this for examples results in row 1 has 500 values.

Now what I want to get is the proportion. Meaning I want to get the total of my group by statement divided by the total of my group by statement with the WHERE filter. Is this possible?

CodePudding user response：

You may use a case expression to count filtered rows in the same group by projection eg

SELECT 
    X, 
    COUNT(
        CASE WHEN Y IS NULL THEN 1 END
    ) as total_when_y_is_null,
    COUNT(*) as total,
    COUNT(
        CASE WHEN Y IS NULL THEN 1 END
    ) / COUNT(*) as desired_value
FROM table
GROUP BY X

Feel free to change the operations as desired.

CodePudding user response：

You need to join both Tables and have now access to both values

SELECT X, (total1/total2) As proportion
FROM
(SELECT X, COUNT(*) as total1
FROM table 
WHERE Y IS NULL 
GROUP BY X) As t2 

JOIN
(SELECT X, COUNT(*) as total2
FROM table
GROUP BY X) As t1 ON t1.X = t2.X