sql - add column with sum of another column to query-CodePudding

i have a rather complex nested query that is structured like this:

select descr, sum(percentage) as perc from
( COMPLEX QUERY HERE)
as A group by descr  order by perc desc

thre result of this query is this:

descr                           perc
------------------------------------
bottom (1 * 18 cm²)             2.77
totalarea (1 * 70 cm²)          2.49
innerwalls (1 * 22 cm²)         2.37
bottom (2 * 12 cm²)             1.55
innerwalls (2 * 17 cm²)         1.51
risperror                       0.29
totalnumberdefects (1 * 30 cm²) 0.00
clipserror                      0.00
outerwalls (1 * 18 cm²)         0.00
outerwalls (2 * 16 cm²)         0.00

Now i would need to add a column, that contains the Sum() of perc, so in this case i would expect this as a result:

descr                           perc        sum
------------------------------------------------
bottom (1 * 18 cm²)             2.77       10.98
totalarea (1 * 70 cm²)          2.49       10.98
innerwalls (1 * 22 cm²)         2.37       10.98
bottom (2 * 12 cm²)             1.55       10.98
innerwalls (2 * 17 cm²)         1.51       10.98
risperror                       0.29       10.98
totalnumberdefects (1 * 30 cm²) 0.00       10.98
clipserror                      0.00       10.98
outerwalls (1 * 18 cm²)         0.00       10.98
outerwalls (2 * 16 cm²)         0.00       10.98

given that the query is really complex and slow, i would like to add the above column by starting by the result of the above query, without having to call the same query again for the results.

How can this be done?

CodePudding user response：

You can use the SUM window function on your result:

WITH myquery AS (
  select descr, sum(percentage) as perc from
  ( COMPLEX QUERY HERE)
  as A group by descr  order by perc desc
)
SELECT *, SUM(perc) OVER () AS "sum"
FROM myquery

CodePudding user response：

Use sum() with as window function:

SELECT *,SUM(perc) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM (your_complex_sql_query) j

The frame RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING is optional in this case - as the frame has no order or partition -, but it is considered a good practice to make things explicit. So you may also just use OVER ()

SELECT *,SUM(perc) OVER ()
FROM (your_complex_sql_query) j

Demo: db<>fiddle

CodePudding user response：

Without using group by:

I used window function sum() to get the total perc and the total perc by descr.

SELECT DISTINCT descr,
       SUM(perc) OVER (PARTITION BY descr) AS perc,
       SUM(perc) OVER () AS "sum"
FROM (COMPLEX QUERY HERE) AS sq;

Or using CTE:

WITH cte AS (COMPLEX QUERY HERE)

SELECT DISTINCT descr,
       SUM(perc) OVER (PARTITION BY descr) AS perc,
       SUM(perc) OVER () AS "sum"
FROM cte;