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
 General SQL Server Forums
 New to SQL Server Programming
 Sub-Query v Group By or (Any other method)

Author  Topic 

nevzab
Starting Member

34 Posts

Posted - 2013-06-18 : 05:12:41
Hi,

I have been using SQL for a couple of years now, primarily to write views / stored procedures for Crystal Reports. I am looking for some advice regarding the summing of values in a select that references multiple tables.

For example...
1. A call is logged (Calls table)
2. Labour events are recorded against the call (Call_Events table)
3. Parts are issued to the call

I need to return...
1.The Call_Ref from the Calls table
2.The sum of Event_Cost from Call_Events
3.The sum of Cost_Price from Call_Parts_Used

I usually use sub-queries as follows:

select

Call_Ref
,(select sum(call_events.event_cost) from call_events where call_events.link_to_call = calls.call_ref)
,(select sum(call_parts_used.cost_price) from call_parts_used where call_parts_used.link_to_call = calls.call_ref)

from calls


Of course, typically, I would be returning a shed load of additional columns that require other tables such as client and equipment related columns.

So, my questions are...

a. Is this the best approach, given there could be numerous tables involved / columns returned?
b. If not then please could you offer advice?

Thanks and I look forward to some advice. :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-18 : 05:16:51
[code]
select
Call_Ref
,eventcost
,costprice
from calls c
inner join (select link_to_call,sum(call_events.event_cost) as eventcost from call_events group by link_to_call) ce
on ce.link_to_call = c.call_ref
inner join (select link_to_call ,sum(call_parts_used.cost_price) as costprice from call_parts_used group by link_to_call ) cp
on cp.link_to_call = c.call_ref
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-18 : 05:17:31
--May be this ?
select
Call_Ref
,sum(call_events.event_cost)
,sum(call_parts_used.cost_price)
from calls
LEFT JOIN call_events ON call_events.link_to_call = calls.call_ref
JOIN call_parts_used ON call_parts_used.link_to_call = calls.call_ref
GROUP BY calls.call_ref




--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-18 : 05:25:43
quote:
Originally posted by bandi

--May be this ?
select
Call_Ref
,sum(call_events.event_cost)
,sum(call_parts_used.cost_price)
from calls
LEFT JOIN call_events ON call_events.link_to_call = calls.call_ref
JOIN call_parts_used ON call_parts_used.link_to_call = calls.call_ref
GROUP BY calls.call_ref




--
Chandu


why left join for one and inner join for other?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-18 : 05:28:37
this is the closest one to main query preserving the correlated subqueries approach

select
Call_Ref
,eventcost
,costprice
from calls c
outer apply (select sum(event_cost) as eventcost from call_events where link_to_call = c.call_ref)ce
outer apply (select sum(cost_price) as costprice from call_parts_used where link_to_call = c.call_ref)cp


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-18 : 05:39:56
[code]Hi visakh,
--Simple illustration
declare @tab1 Table(col1 int, col2 int)
insert into @tab1 values(1, 10), (2, 20), (3,30)

declare @tab2 Table(col1 int, col2 int)
insert into @tab2 values(1, 100), (3,300)

declare @tab3 Table(col1 int, col2 int)
insert into @tab3 values(1, 110), (2, 210)

--My solution with one modification
select
t1.col1
,sum(t2.col2) eventcost
,sum(t3.col2) costprice
from @tab1 t1
LEFT JOIN @tab2 t2 ON t1.col1 = t2.col1
LEFT JOIN @tab3 t3 on t3.col1 = t1.col1
GROUP BY t1.col1
/*
col1 eventcost costprice
1 100 110
2 NULL 210
3 300 NULL*/


--Your Solution
SELECT
c.col1
,eventcost
,costprice
from @tab1 c
inner join (select col1,sum(col2) as eventcost from @tab2 group by col1) ce on ce.col1= c.col1
inner join (select col1,sum(col2) as costprice from @tab3 group by col1) cp on cp.col1= c.col1
/*
col1 eventcost costprice
1 100 110
*/

-- OP's colution
select
c.Col1
,(select sum(col2) from @tab2 where c.col1 = col1)
,(select sum(col2) from @tab3 where c.col1 = col1)
from @tab1 c
/*
col1 eventcost costprice
1 100 110
2 NULL 210
3 300 NULL*/

[/code]

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-18 : 05:43:34
why not include my last suggestion too? its the closest to OPs original suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-18 : 05:46:07
quote:
Originally posted by visakh16

why not include my last suggestion too? its the closest to OPs original suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



I haven't seen that before my earlier post

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-18 : 05:58:23
quote:
Originally posted by bandi

quote:
Originally posted by visakh16

why not include my last suggestion too? its the closest to OPs original suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



I haven't seen that before my earlier post

--
Chandu


Ok..Thats fine
I understood reason for the usage of LEFT join, my only query was why you retained second one as inner join itself. Anyways your illustration had it clarified

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

nevzab
Starting Member

34 Posts

Posted - 2013-06-18 : 06:44:05
Thanks very much for your alternative examples, however, my SQL returns the same result set as all your examples (where left join is used) so what I would like to know now is which solution is the best and why?

Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-18 : 06:45:49
both left join and outer apply solutions are best ones, dont think there's much to choose between them. You can probably test them for a sample data to compare their performance

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

nevzab
Starting Member

34 Posts

Posted - 2013-06-18 : 06:55:02
Okay, thanks you all for your help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-18 : 06:57:50
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -