Add sum column to query

What's the best way to include an amount sum in a query if I also need the individual amounts? For example, I need the following columns:

order number

order amount

total amount

I tried using "with cube ", but the total number of columns in the query exceeds the allowable limit of 10.

select a.ordernum, a.orderamt, b.totalamt
from orders a, (select ordernum, sum(orderamt) from orders group by ordernum) b
where a.ordernum = b.ordernum

This will return data like:

ORDERNUM ORDERAMT TOTALAMT
1234 12.33 56.23
1234 11.25 56.23
1234 32.65 56.23
2345 ...ETC...|||

Hello,

Can you list the schema and an example of required output briefly as I'm not 100% sure what you want to do: do you want to select an order amount and sum what amount?

Sounds as if you should be using a subquery/join.

Cheers,

Rob

|||

to complete Phil's answer, I think this should be a little more efficient to do this

SELECT a.ordernum, a.orderamt, b.totalamt, c.totalamt...
FROM orders a
inner join (select ordernum, sum(orderamt) from orders group by ordernum) b on a.ordernum= b.ordernum
inner join (select ordernum, sum(orderamt) from orders group by ordernum) c on a.ordernum= c.ordernum

|||So you've just added a duplicate total column. Why?|||

Could it look something like this?

create table #x( ordernum int not null, amt int not null )
insert #x
select 1, 20 union all
select 1, 15 union all
select 2, 35 union all
select 2, 10 union all
select 2, 70
go

select ordernum, amt, sum(amt) as total
from #x
group by ordernum, amt
with rollup
go

ordernum amt total
-- -- --
1 15 15
1 20 20
1 NULL 35
2 10 10
2 35 35
2 70 70
2 NULL 115
NULL NULL 150

(8 row(s) affected)

/Kenneth

|||I tried using With Cube and With rollup, but they have a limit of 10 on the number of columns returned. The actual report I'm writing has over 20.|||So what are you trying to achieve?

Give us an example output of what you'd like the data to look like.|||I think your first post will solve the problem. Haven't had time to try it yet.|||

sorry, i made a mistake...

SELECT a.ordernum, a.orderamt, b.totalamt
FROM orders a
inner join (select sum(orderamt) from orders) b on a.ordernum= b.ordernum

I thought inner join could be more efficient than using where statement...

|||

stephane - Montpellier wrote:

sorry, i made a mistake...

SELECT a.ordernum, a.orderamt, b.totalamt
FROM orders a
inner join (select sum(orderamt) from orders) b on a.ordernum= b.ordernum

I thought inner join could be more efficient than using where statement...

Your statement won't work there either. You didn't include ordernum in your subquery.

Never-the-less, inner join and the join method I used are identical.