Get grouped value divided by total

Question

I have a table like this :

+----+---------------+----------+
| id | city          | price    |
+----±---------------±----------+
| 1  | Paris         | 2.000,00 |
| 2  | London        | 500,00   |
| 3  | Paris         | 500,00   |
| 4  | Madrid        | 1.000,00 |
±----±---------------±----------±

And a request like this :

select
    city,
    sum(price)
from orders
group by city
order by sum(price) desc

This gives me a result like :

+----------+---------------+
| city     | SUM(price)    |
+----------±---------------±
| Paris    | 2.500,00      |
| Madrid   | 1.000,00      |
| London   | 500,00        |
±----------±---------------±

What I would like to have is the ratio of the price for each city in a third column, so that Paris would have 62.50 % and so on, like this :

+----------+---------------+-------------+
| city     | SUM(price)    | Ratio       |
+----------±---------------±-------------+
| Paris    | 2.500,00      | 62,50       |
| Madrid   | 1.000,00      | 25          |
| London   | 500,00        | 12,50       |
±----------±---------------±-------------±

Currently, I have to calculate the last column in PHP after getting the first resultset. I was wondering if there was any way I could do this directly in SQL ?

Answer 1

I suggest doing with a CTE to improve reading, but you will get same performance as Giorgios answer.

WITH cte0 as (
     SELECT *
     FROM Orders
     WHERE <filters>
),
cte as (
     SELECT SUM(price) total
     FROM cte0 
)
SELECT city, sum(price), 100.0 * SUM(Price) /  cte.total
FROM cte0 
CROSS JOIN cte
GROUP BY city

Answer 2

You can use a subquery to get the total price:

select
    city,
    sum(price),
    100.0 * sum(price) / (select sum(price) from Orders) AS Ratio
from Orders
group by city
order by sum(price) desc

Demo here

Alternatively you can use a CROSS JOIN:

select
    city,
    sum(price),
    100.0 * sum(price) / t.total_price AS Ratio
from Orders
cross join (select sum(price) as total_price from Orders) AS t
group by city
order by sum(price) desc