Subquery sum - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Subquery sum

Author

Topic

Deltaur
Starting Member

29 Posts

Posted - 2010-06-01 : 03:08:44

Hi,

I got little problem with figuring out how to build subquery.

Table A, i got here order header information like customer name, address, order number and name of the salesman.
OrderNum,CustomerName,Salesman
12345,TedTheTest,Superman

Table B i got all the transactions which is binded to Table A order number this is kinda bookkeeping table where all the transactions is stored.
OrderNum,AccountNum,Sum
12345,3000,1200
12345,3000,4000
12345,3000,1000
12345,3000,500
12345,5100,-500
12345,5100,-1000

Now the problem is that i got to have one line per order where i have sum these transactions, like this:
OrderNum,Customer,Salesman,SalesTotal,PurhaseTotal
12345,TedTheTest,Superman,6700,-1500

Sachin.Nand

2937 Posts

Posted - 2010-06-01 : 03:50:55

Why should -1500 be shown as different column & how is that value coming?

PBUH

Deltaur
Starting Member

29 Posts

Posted - 2010-06-01 : 03:54:30

Because i have to split these purhaces and sales. And account range for sales is 3000-3200 and purhaces account range is 5000-5100. So when transaction is entered to Table B it haves account number.

So, i need also to sum certain account range for sales and purhaces for certain order.

Sachin.Nand

2937 Posts

Posted - 2010-06-01 : 04:05:13

[code]
declare @tbl as table(OrderNum int,AccountNum int,Sums int)
insert into @tbl
select 12345,3000,1200 union all
select 12345,3000,4000 union all
select 12345,3000,1000 union all
select 12345,3000,500 union all
select 12345,5100,-500 union all
select 12345,5100,-1000

select * from @tbl

select * from
(
select distinct ordernum,
(select sum(Sums) from @tbl t1 where t1.OrderNum=t2.OrderNum and accountNum between 3000 and 3200),
(select sum(Sums) from @tbl t1 where t1.OrderNum=t2.OrderNum and accountNum between 5000 and 5100)
from @tbl t2
)T put your join condition here with Table A

[/code]

PBUH

Deltaur
Starting Member

29 Posts

Posted - 2010-06-01 : 05:03:29

Thank you!

I will try to figure this out. All i can get now is

Msg 8155, Level 16, State 2, Line 4
No column was specified for column 2 of 'T'.
Msg 8155, Level 16, State 2, Line 4
No column was specified for column 3 of 'T'.

And i dont know how to build that join condition :(

Sachin.Nand

2937 Posts

Posted - 2010-06-01 : 05:11:00

[code]
select * from
(
select distinct ordernum,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 3000 and 3200)as SalesTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 5000 and 5100)as PurhaseTotal
from TableB t2
)T inner join TableA on TableA.OrderNum=T.OrderNum
[/code]

PBUH

Deltaur
Starting Member

29 Posts

Posted - 2010-06-01 : 05:20:16

AWESOME! Thank you!

It works! what was the procedure to turn NULL values to 0?

and can i use somewhere the where conditions like example "where date is between '2010-01-01' and '2010-06-01'"

and again thank you very very much!

Sachin.Nand

2937 Posts

Posted - 2010-06-01 : 05:49:52

accountNum between 3000 and 3200 and your date condition
You have to use in both the queries for SalesTotal and PurhaseTotal.
For NULL use you can use isnull(sum(Sums),0)

PBUH

Deltaur
Starting Member

29 Posts

Posted - 2010-06-01 : 06:06:09

Cool, that works also fine.

Can i use in this clause,

select * from
(
select distinct ordernum,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 3000 and 3200)as SalesTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 5000 and 5100)as PurhaseTotal
from TableB t2
)T inner join TableA on TableA.OrderNum=T.OrderNum

as much i like those select statements?

i will try to build stored procedure for this one and try to but some date and salesman variables also.

Sachin.Nand

2937 Posts

Posted - 2010-06-01 : 06:11:20

quote:
Originally posted by Deltaur

Cool, that works also fine.

Can i use in this clause,

select * from
(
select distinct ordernum,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 3000 and 3200)as SalesTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 5000 and 5100)as PurhaseTotal
from TableB t2
)T inner join TableA on TableA.OrderNum=T.OrderNum

as much i like those select statements?

Sorry I dint understand what you want.

PBUH

Deltaur
Starting Member

29 Posts

Posted - 2010-06-01 : 06:18:03

sorry for my bad english but i meant that can i use this query like this :

select * from
(
select distinct ordernum,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 3000 and 3200)as SalesTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 5000 and 5100)as PurhaseTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 6000 and 6200)as PaymentTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 7000 and 7200)as DeliveryTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 8000 and 8200)as UpkeepTotal
from TableB t2
)T inner join TableA on TableA.OrderNum=T.OrderNum

Sachin.Nand

2937 Posts

Posted - 2010-06-01 : 07:45:47

Yes you can.Just make sure you put the where condition for dates in the inner queries.

PBUH

Deltaur
Starting Member

29 Posts

Posted - 2010-06-02 : 01:26:52

That is working fine now. I was wondering is it possible to add some where statement for the Table A (where i have salesmans and customers)?.

select * from
(
select distinct ordernum,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 3000 and 3200)as SalesTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 5000 and 5100)as PurhaseTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 6000 and 6200)as PaymentTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 7000 and 7200)as DeliveryTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 8000 and 8200)as UpkeepTotal
from TableB t2
)T inner join TableA on TableA.OrderNum=T.OrderNum

Where TableA.salesman = 'SuperTed' and TableA.customer='Batman'

like that?

Sachin.Nand

2937 Posts

Posted - 2010-06-02 : 02:53:03

Yes you can.


select * from
(
select distinct ordernum,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 3000 and 3200)as SalesTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 5000 and 5100)as PurhaseTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 6000 and 6200)as PaymentTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 7000 and 7200)as DeliveryTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 8000 and 8200)as UpkeepTotal
from TableB t2
)T inner join TableA on TableA.OrderNum=T.OrderNum 
Where TableA.salesman = 'SuperTed' and TableA.customer='Batman'

PBUH

Deltaur
Starting Member

29 Posts

Posted - 2010-06-02 : 05:21:19

Awesome!,

More questions :)

I created inner join to third table lets call that TableC and everything is working fine but there comes some error in my reporting platform that ordernum is already there.

select * from
(
select distinct ordernum,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 3000 and 3200)as SalesTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 5000 and 5100)as PurhaseTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 6000 and 6200)as PaymentTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 7000 and 7200)as DeliveryTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 8000 and 8200)as UpkeepTotal
from TableB t2
)T inner join TableA on TableA.OrderNum=T.OrderNum
inner join TableC on TableC.OrderNum=T.OrderNum

Where TableA.salesman = 'SuperTed' and TableA.customer='Batman'

Can i exclude from TableC that it wont get that ordernum column?
With TableC i have to count 3 values together but where i put that third statement?

should it be after that T "function"? or can i put it inside the T "function"?

(select sum(value1 + value2 + value3)
from tableC t3 where t3.ordernum = t2.ordernum) as TotalInstall2

i hope you get somekind picture what i mean :)

EDIT:

I got it to work :)

I just put that third tableC statement inside the "function" :)

But that is still open can i exclude columns that i dont want in result set?

Sachin.Nand

2937 Posts

Posted - 2010-06-02 : 05:34:16

You will have to just mention the column names the ones you want in the resultset.

Example T.SalesTotal,TableA.SomeColumname,TableC.OtherColumname and so on

PBUH

Deltaur
Starting Member

29 Posts

Posted - 2010-06-02 : 08:30:48

hmm, one problem came up..

(select isnull(sum(sums),0)
from tableB t1
where t1.ordernum=t2.ordernum
and accnro= 1234 and accnro= 1230 and accnro= 1501) as TOTAL

from tableB t2

it returns me 0 and i have checked that there is transactions in those account numbers.

EDIT:

Stupid error, i should use "AND accnro in (1234,1230,1501)"

:-D

Sachin.Nand

2937 Posts

Posted - 2010-06-02 : 08:51:36

You have to show the data where you think its breaking and the whole query that you are using.

PBUH

Deltaur
Starting Member

29 Posts

Posted - 2012-01-20 : 02:51:14

Hi,

Two years has passed and this query has been worked like a charm. Now i have to tune it up little bit.

select * from
(
select distinct ordernum,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 3000 and 3200)as SalesTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 5000 and 5100)as PurhaseTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 6000 and 6200)as PaymentTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 7000 and 7200)as DeliveryTotal ,
(select sum(Sums) from TableB t1 where t1.OrderNum=t2.OrderNum and accountNum between 8000 and 8200)as UpkeepTotal
from TableB t2
)T

inner join TableA on TableA.OrderNum=T.OrderNum
Where TableA.salesman = 'SuperTed' and TableA.customer='Batman'

If there is no data in T statement it wont return nothing, so question is how i can return ex. 0 for those inside T statement if there is no data? that way i would achieve that i get only TableA.orderNum with its values and 0 value for in T statement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources