CODEWITHSUNDEEP
sqlpostgresqlsum
sharkyenergy
sharkyenergy

Reputation: 4183

sql - add column with sum of another column to query

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?

Upvotes: 0

Views: 1379

Answers (3)

Jim Jones
Jim Jones

Reputation: 19693

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

Upvotes: 1

nachospiu
nachospiu

Reputation: 2049

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;

Upvotes: 0

id&#39;7238
id&#39;7238

Reputation: 2681

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

Upvotes: 4

Related Questions