I want to calculate the percentage of the sum of the top 5 countries by number of customers compared to the total number of customers. The SQL Editor rounds decimals, therefore the counter of my calculation needs to be multiplied by 100 before dividing it.
I have tried the following SQL statement:
SELECT A.NUM, A.DENOM, cast(A.NUM as float)/cast(A.DENOM as float) FROM ( SELECT (SELECT SUM(count_five) * 100 FROM( SELECT *, COUNT(*) AS count_five FROM Customers GROUP BY Country ORDER BY count_five DESC LIMIT 5) AS NUM, (SELECT SUM(count_all) * 100 FROM( SELECT *, COUNT(*) AS count_all FROM Customers GROUP BY Country) AS DENOM )A
Here is the table:
https://www.w3schools.com/sql/trysql.asp?filename=trysql_asc
Advertisement
Answer
Hmmm . . . This would use window functions and conditional aggregation:
SELECT SUM(CASE WHEN seqnum <= 5 THEN cnt END) / SUM(cnt) as top_5_ratio FROM (SELECT Country, COUNT(*) AS cnt, ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) as seqnum FROM Customers GROUP BY Country ) c;
Note that your code has SELECT *
with GROUP BY
. That is pretty close to blasphemy in SQL — it is a construct that generates an error in almost all databases. The unaggregated SELECT
expressions need to be consistent with the GROUP BY
expressions in an aggregation query.