Skip to content
Advertisement

How can I get the percentage of the sum of the top 5 countries in SQL?

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.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement