Summing a column as a seperate column in the same query

[NewlytoSQL]

Hi all, im fairly new to SQL and im stuck half way through a query,
im using DB2 here is what im tryng to do.
i have a query that brings back an item number , shelf req, sum of all orders columns, based on the shelf req column the item number column has more than one row for the same item number therefore my sum of all orders is summing them based on shelf req which i need, what i cant get is how to keep this info plus get the sum of all orders for each item number reqardeless in the same query.
in another word, how do i get the sumallordrs column to sum Sumofallordrs column as seperate column.

ITEM Shelf Req Sumofallordrs SumallItems
1111 20 300 ?
2222 10 200
3333 25 75
1111 25 100

[Purple]

Hi NewlytoSQL and welcome to TSDN DB2 Forum

I have moved your post the from Articles into the forum

Can you post the SQL query you have so far to allow the experts a better opportunity of providing a relevant answer.

Regards Purple

Moderator

[cburnett]

If I understand you correctly, I think the following is what you are after. The key feature here is the ROLLUP function which subtotals the orders by item number. This is the row with the null shelf_req. If you want some other value, use the coalesce function (e.g. coalesce(shelf_ req,9999) in the select list.

Note that you can ignore everything up to the select statement - the with clause just creates a temporary tabe for testing the query out.

with orders(item, shelf_req, order) as (values (1111,20,100),(1111,20,200),(1111,25,100),(2222,10,200),(3333,25,50),(3333,25,25))
select item, shelf_req, sum(order) as sumofallordrs
  from orders
 group by item, rollup(shelf_req)
 order by 1,2

[cburnett]

Oh, and if you want the data on the same row, then you need to use OLAP functions:

with orders(item, shelf_req, order) as (values (1111,20,100),(1111,20,200),(1111,25,100),(2222,10,200),(3333,25,50),(3333,25,25))
select distinct item, shelf_req, sum(order) over(partition by item, shelf_req) as sumbyshelf, sum(order) over(partition by item) as sumbyitem
  from orders
 order by 1,2