SQL relative percentage for each country

I have a table: maps_query like below:

CREATE TABLE maps_query 
(
    id           int 
    day          varchar
    search_query varchar
    country      varchar
    query_score  int
)

The question is to output the relative percentage of queries for maps_query for each country.

Desired output is like below:

country     pct
----------------
CA          0.13
FR          0.45
GB          0.21

I don’t quite understand what relative percentage is here but I assumed it’s asking to output (a country’s search_query counts/ all search_query for all countries)?

Would something like the following work?

Select 
    country, 
    (sum(search_query) / sum(search_query) over () * 100) pct
From 
    map_search
Group by 
    country 

Answer

You almost have it. Here’s your SQL adjusted slightly:

SELECT country
     , SUM(query_score) / (SUM(SUM(query_score)) OVER ()) AS pct
     , SUM(query_score)
     , SUM(SUM(query_score)) OVER ()
  FROM map_search
 GROUP BY country
;

The result, using some test data:

+---------+--------+------------------+-------------------------------+
| country | pct    | SUM(query_score) | SUM(SUM(query_score)) OVER () |
+---------+--------+------------------+-------------------------------+
| C1      | 0.5323 |             3300 |                          6200 |
| C2      | 0.4677 |             2900 |                          6200 |
+---------+--------+------------------+-------------------------------+

search_query wasn’t a numeric type. I think you meant query_score.

No need to multiply by 100, if your expected result is not a percent, but just the fraction between 0 and 1.

Your use of a window function wasn’t quite valid, since you tried to SUM OVER a non-aggregate (expression not functionally dependent on the GROUP BY terms).

I resolved that by using SUM(query_score) as the expression to use in the window function argument.