SQL - Using the With Rollup data in a calculation

Question

I have query results shown below. The last row is the result from With Rollup.

Product    Total
5300          1
5600         13
5400         66
5700        200
NULL        280

Is there a way to use the Rollup data (280) in a calculation to find the percentage of the total? So I get this:

Product    Total     %
5300          1     3%
5600         13     5%
5400         66    22%
5700        200    72%
NULL        280

Yes it should be. I just slapped a 1% in there as an example. I did an edit. — Tug Strongly, Sep 09 '15 at 18:10
possibly using a window function or using a cte`With CTE as (yourQUERY) Select Product, Total, total/(Select total from cte where product is null)*100 from cte` thinking about window now... — xQbert, Sep 09 '15 at 18:10
http://stackoverflow.com/questions/12931118/calculate-running-percentage-in-sql — xQbert, Sep 09 '15 at 18:19

score 0 · Answer 1 · answered Sep 09 '15 at 18:15

0

Should be easy with a CTE. Now depend on how strict are on your output.

SQL FIDDLE DEMO

WITH total as (
     SELECT Total
     FROM sumarize
     WHERE Product IS NULL
 )
 SELECT S.*, (S.Total * 1.0 / t.Total)*100 as porcent
 FROM sumarize S, total t

answered Sep 09 '15 at 18:15

Juan Carlos Oropeza

47,252
12
78
118

score 0 · Answer 2 · answered Sep 09 '15 at 18:21

0

Use a windowing function like this

SELECT (Total/(SUM(Total) OVER(PARTITION BY Product)))*100 AS Percent

answered Sep 09 '15 at 18:21

Hogan

69,564
10
76
117

I'm not sure if that is the right way. First i couldnt copy the line on my fiddle to try it. And the denominator should be always the same, so not sure if calculate the `SUM() over partition` will give you that. – Juan Carlos Oropeza Sep 09 '15 at 19:33
ok, so you don't want to partition it? then just use `SUM(total) OVER ()` – Hogan Sep 09 '15 at 19:36

SQL - Using the With Rollup data in a calculation

2 Answers2