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