Share
Go down
avatar
Admin
Posts : 155
Join date : 2017-11-11
Age : 27
View user profilehttp://net-media.yoo7.com

SQL Percentage of each values from sum of row

on Wed Dec 13, 2017 3:13 pm
I'm trying to get the percentage of each values from this query. I know this question might be a duplicate, but I cannot manage to make it work.
Code:
SELECT SUM(distance) AS SUM_DISTANCE FROM table1 GROUP BY type
It gives me this:
Code:
+------------------+
|  SUM_DISTANCE  |
+------------------+
| 60159.7965819000 |
|  289.0123500000 |
| 34660.5349672000 |
+------------------+
Here is my attempt:
Code:
WITH Total AS
    (SELECT SUM(distance) AS SUM_DISTANCE FROM table GROUP BY type)
select
    (total.SUM_DISTANCE / sum(distance)) * 100 AS PercentDistance
FROM table1, Total
GROUP BY Total.SUM_DISTANCE;
This is the result of my attempt:
+-----------------+
| PercentDistance |
+-----------------+
| 0.001300 |
| 0.159900 |
| 0.277600 |
+-----------------+

Answers

Use window functions:
Code:
SELECT CAST(100 * SUM(distance) / SUM(SUM(distance)) OVER () AS DECIMAL(5, 2)) AS percentage
FROM table1
GROUP BY type;
Back to top
Permissions in this forum:
You can reply to topics in this forum