Menu
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 ?
854
SQL SUM() function with group bySUM is used with a GROUP BY clause. The aggregate functions summarize the table data. Once the rows are divided into groups, the aggregate functions are applied in order to return just one value per group.
Use DataFrame. groupby(). sum() to group rows based on one or multiple columns and calculate sum agg function. groupby() function returns a DataFrameGroupBy object which contains an aggregate function sum() to calculate a sum of a given column for each group.
There is no built-in operator that calculates percentages in SQL Server. You have to rely on basic arithmetic operations i.e. (number1/number2 x 100) to find percentages in SQL Server.
The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
