November 10, 2005 at 11:31 am
I have column, in this way
ProductID Quantity
1 5
2 10
sum 15
2 10
2 25
sum 35
3 15
3 30
sum 45
sum 95
I want a seperate column for only sums,how can I compute only sums in another new column
November 10, 2005 at 11:54 am
I am using the follwing query to get my result order by productid, but I want the result set in a seperate column, how can i do that, can any one pls help?
select productid,quantity,totals from products order by productidid
compute sum(quantity) by productidid
compute sum(quantity)
November 10, 2005 at 12:30 pm
Any help?
I need the result set of the above query in the column totals.
November 10, 2005 at 1:01 pm
not sure if this is what you want:
select p.productid, p.quantity, t.totals
from
(select distinct productid,quantity
from products ) p
join
( select productid, sum(quantity) totals ) t
on p.productid = t.productid
order by p.productid
if it is not, can you post the desired output?
* Noel
November 10, 2005 at 1:04 pm
My output shud be inthis way
ProductID Quantity Totals
1 5
1 10 15
2 10
2 25 35
3 15
3 30 45
95
November 10, 2005 at 1:25 pm
You are a lot better doing that formating on the client side!!
* Noel
November 10, 2005 at 2:01 pm
so..it means we cant do it at database?
November 10, 2005 at 2:13 pm
with your current specifications there is no way to doit if there is more than one product id with the same quantity, otherwise you can use a variation of what I posted as:
select p.productid, p.quantity, t.totals
from
(select distinct productid,quantity
from products ) p
left join
( select productid, max(quantity) mq, sum(quantity) totals ) t
on p.productid = t.productid and p.quantity = t.mq
union all
select null, null, sum(quantity)
from products
order by p.productid
but that is not efficient neither should be the function of the database. this is more a presentation issue!!
* Noel
November 10, 2005 at 2:28 pm
OK, thanks for ur replies, and have a gud week end
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply