SQL Group By with calculator task

Question

I want to calculate the percent of the fee with group by with city and country

City       Country   Percent
New York   US        ?

I have a table that looks like this:

ID	Name	City	Country	Fee
1	Mike	NY	USA	1000$
1	Kaia	DL	USA	2000$
1	Lee	HCM	VN	3000$
1	Yuna	PK	VN	4000$

I want to get the percent of fee from customers in NY in comparison to anyone in the USA.

Like here 1000$ from NY/1000$+2000$ from USA.

How should I go about querying this?

Answer 1

Here is a solution coded on SQL server. It will run on most RDBMS.

create table fees(
ID int,
Name varchar(10),
City varchar(10),
Country varchar(10),
Fee int);
insert into fees values
(1,'Mike','NY','USA',1000),
(1,'Kaia','DL','USA',2000),
(1,'Lee','HCM','VN',3000),
(1,'Yuna','PK','VN',4000);

SELECT
  ID,
  Name,
  City,
  f.Country,
  Fee,
  100*Fee/sf  pourcent_country
FROM fees f
LEFT JOIN (SELECT country, SUM(fee) sf FROM fees GROUP BY country) c
ON f.country = c.country
ORDER BY Country, City

ID | Name | City | Country |  Fee | pourcent_country
-: | :--- | :--- | :------ | ---: | ---------------:
 1 | Kaia | DL   | USA     | 2000 |               66
 1 | Mike | NY   | USA     | 1000 |               33
 1 | Lee  | HCM  | VN      | 3000 |               42
 1 | Yuna | PK   | VN      | 4000 |               57

db<>fiddle here