By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,018 Members | 930 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,018 IT Pros & Developers. It's quick & easy.

Summing a column as a seperate column in the same query

P: 1
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
Jul 23 '07 #1
Share this Question
Share on Google+
3 Replies


Purple
Expert 100+
P: 404
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
Aug 15 '07 #2

P: 57
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.

Expand|Select|Wrap|Line Numbers
  1. 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))
  2. select item, shelf_req, sum(order) as sumofallordrs
  3.   from orders
  4.  group by item, rollup(shelf_req)
  5.  order by 1,2 
Aug 16 '07 #3

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

Expand|Select|Wrap|Line Numbers
  1. 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))
  2. select distinct item, shelf_req, sum(order) over(partition by item, shelf_req) as sumbyshelf, sum(order) over(partition by item) as sumbyitem
  3.   from orders
  4.  order by 1,2 
Aug 16 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.