Search
Learn
Share

Each Row's % of the Sum of That Column for A Table

Published by Profile Image Ethan on 16 August, 2016
50 views

Breaking the Rules


Traditionally, it is taught that you can not use the result of an aggregate function such as SUM with each row of a table. This makes it theoretically impossible to calculate the percentage for each row of a table without stepping out of SQL. I was determined, and found a way to break the rule.

DROP VIEW IF EXISTS PROJECT_TIMES_V;
CREATE VIEW PROJECT_TIMES_V AS
SELECT (SUM(Days) + (SUM(Hours) / 24) + (SUM(Minutes) / 1440)) AS _Days,
(SUM(Hours) + (SUM(Minutes) / 60)) AS _Hours,
(SUM(Minutes) % 60) AS _Minutes,
((SUM(Days) * 1440) + (SUM(Hours) * 60) + SUM(Minutes)) AS _Ttl_Minutes,
MIN(StartDate) AS _StartDate,
MAX(EndDate) AS _EndDate,
Project_Name
FROM Heartbeat_T
JOIN Project_Heartbeat_T ON (Heartbeat_ID = Project_Heartbeat_ID)
JOIN Projects_T ON (Project_Heartbeat_T.Project_ID = Projects_T.Project_ID)
GROUP BY Project_Heartbeat_T.Project_ID;


WITH RECURSIVE
cnt(x, y) AS (SELECT 1, SUM(PROJECT_TIMES_V._Ttl_Minutes) FROM PROJECT_TIMES_V
UNION SELECT x+1, y FROM cnt WHERE x<(SELECT COUNT(*) FROM PROJECT_TIMES_V))
SELECT _Days, _Hours, _Minutes, _StartDate, _EndDate, Project_Name,
(CAST(_Ttl_Minutes AS FLOAT) / y) AS _PC FROM cnt JOIN PROJECT_TIMES_V
GROUP BY Project_Name ORDER BY _PC DESC;