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
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.