SQL relative percentage for each country-CodePudding

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

CodePudding user response：

First compute total search query and then use that total to get relative percentage for each country.

with total as
           ( select sum(search_query) as total
           from maps_query)

select country,
       search_query / total.total 
       from maps_query,total

CodePudding user response：

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.

CodePudding user response：

The search_query seems to be a query string. You cannot sum it, it's not a number. What you probably want to do is count queries per country.

Apart from this your query looks quite fine, but if search_query really were a number to add up then you'd have to calculate the sum and the sum of the sums: sum(search_query) / sum(sum(search_query)) over ().

Here is your query corrected:

Select 
    country, 
    (count(*) / sum(count(*)) over () * 100) as pct
From 
    map_search
Group by 
    country 
Order by 
    country;