CODEWITHSUNDEEP
sql
User63164
User63164

Reputation: 103

SQL Calculate group percentage rather than overall percentage

Apologies if this is a duplicated question, but I couldn't find any solutions that I understood and fit my example problem

What I'm trying to do:

I have a table containing raw user data and I want to summarise how many users per country are paid users.

There is a column in the table called 'Status' which is either 'Paying Customer' or 'Free Trial'.

What I have tried myself

SELECT COUNTRY,
 COUNT(COUNTRY) AS USER_COUNT,
 COUNT(COUNTRY) * 100 / (SELECT COUNT(*) FROM CUSTOMER_DETAILS WHERE STATUS = 'Paying Customer') AS PAID_USER_PCT
FROM CUSTOMER_DETAILS
GROUP BY COUNTRY
ORDER BY USER_COUNT DESC`

The result

For some reason, the 'PAID_USER_PCT calculation is returning the percentage of the whole dataset rather than just for that country.

Any ideas on how I can tweak my code to achieve this?

Upvotes: 0

Views: 19

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

I have a table containing raw user data and I want to summarise how many users per country are paid users.

Use conditional aggregation. If you also want the ratio:

SELECT COUNTRY, COUNT(*) as USER_COUNT,
       SUM(CASE WHEN STATUS = 'Paying Customer' THEN 1 ELSE 0 END) as Paying_customers,
       AVG(CASE WHEN STATUS = 'Paying Customer' THEN 1.0 ELSE 0 END) as ratio_paying
FROM CUSTOMER_DETAILS
GROUP BY COUNTRY
ORDER BY USER_COUNT DESC;

Upvotes: 1

Related Questions