CODEWITHSUNDEEP
sqlgroup-bysumamazon-athenapresto
Helpplease
Helpplease

Reputation: 37

SQL: How do you Sum a Sum column?

My query returns the columns I need, but I'm wondering if there's a way to get another column that will return the sum of the: sum(CAST(qty AS int)) as qty column. It's ok if it is listed for every row; I just want a way to not have to pull out the calculator and add up all of the sum(CAST(qty AS int)).

select sql1.item_code, sql1.supplier_so, sum(sql1.qty) Qty, sql1.dt
from
(SELECT supplier_so, item_code, sum(CAST(qty AS int)) as qty, dt FROM "table.name"
where
supplier_so = 'XXXXXXX'
group by supplier_so, item_code, dt
UNION
SELECT supplier_so, item_code, sum(CAST(qty AS int)) as qty, dt FROM "table.name2"
where
supplier_so = 'XXXXXXX'
and
dt < date '2021-06-01'
 group by supplier_so, item_code, dt
) sql1
group by supplier_so, item_code, dt

Upvotes: 0

Views: 65

Answers (2)

psreddyio
psreddyio

Reputation: 80

Why can't you try this.

with sql1 as 
(SELECT supplier_so, item_code, sum(CAST(qty AS int)) as qty, dt FROM "table.name"
where
supplier_so = 'XXXXXXX'
group by supplier_so, item_code, dt
UNION
SELECT supplier_so, item_code, sum(CAST(qty AS int)) as qty, dt FROM "table.name2"
where
supplier_so = 'XXXXXXX'
and
dt < date '2021-06-01'
 group by supplier_so, item_code, dt
) 
select sql1.item_code, sql1.supplier_so, sql1.dt, sum(sql1.qty) over () from sql1;

Upvotes: 0

Martin Traverso
Martin Traverso

Reputation: 5316

You can use a window function for this:

SELECT 
   ..., 
   SUM(CAST(qty AS int)) OVER ()
FROM ...

Upvotes: 1

Related Questions