SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sub-Query v Group By or (Any other method)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nevzab
Starting Member

United Kingdom
34 Posts

Posted - 06/18/2013 :  05:12:41  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 06/18/2013 :  05:16:51  Show Profile  Reply with Quote

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


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

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 06/18/2013 :  05:17:31  Show Profile  Reply with Quote
--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

India
52309 Posts

Posted - 06/18/2013 :  05:25:43  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 06/18/2013 :  05:28:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 06/18/2013 :  05:39:56  Show Profile  Reply with Quote
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*/


--
Chandu

Edited by - bandi on 06/18/2013 05:41:30
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/18/2013 :  05:43:34  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 06/18/2013 :  05:46:07  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 06/18/2013 :  05:58:23  Show Profile  Reply with Quote
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

United Kingdom
34 Posts

Posted - 06/18/2013 :  06:44:05  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 06/18/2013 :  06:45:49  Show Profile  Reply with Quote
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

United Kingdom
34 Posts

Posted - 06/18/2013 :  06:55:02  Show Profile  Reply with Quote
Okay, thanks you all for your help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/18/2013 :  06:57:50  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000