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
 approach to calculating two field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dave1816
Starting Member

United Kingdom
9 Posts

Posted - 12/13/2012 :  10:29:52  Show Profile  Reply with Quote
Hi I’m pretty basic at SQL and having trouble trying to calculate a sub total of a field which is the following

"
sum(InItem.qty_Invoiced  * InItem.cost) as "Total Cost"

Not sure if a loop of some kind is required? This is due to the fact that I have to connect to two tables Inv_Item and Inv_hdr via inv_num where an you can have more than one invoice number (seperated by invoice line no).

Therefore i need something which would say
For each invoice number listed from Inv_hdr in Inv_Item do
sum(InItem.qty_Invoiced * InItem.cost) as "Total Cost"
and obvously assign it to some variable to hold the total figure.

Not sure how to do this which is why it properly not very clearer but if you can point me in the right direction taht would be great. My code so far is the following

select inv.Cust_num,

COUNT(inv.Cust_num)AS "Total Orders" 
--sum(InItem.qty_Invoiced  * InItem.cost) as "Total Cost"


from inv_hdr inv
inner join custaddr cust on inv.cust_num = cust.cust_num 
--left join inv_item InItem on  InItem.inv_num  =  inv.inv_num 
where cust.cust_seq = '0' and inv.cust_Num ='FER001S'
group  by inv.Cust_num, cust.name


If this dosn't make sense let me know and ill try explain it clearer


many thanks


D.ARNOLD

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/13/2012 :  10:49:23  Show Profile  Reply with Quote
Select cust.Cust_num,cust.name,
COUNT(distinct inv.Cust_num)AS "Total Orders" 
Sum(inv.qty_Invoiced * inv.cost) as "Total Cost"
from custaddr cust
inner join
(
Select distinct cust_Num,isnull(qty_Invoiced,0)qty_Invoiced,isnull(cost,0)cost
from inv_hdr inv
left join inv_item InItem on  InItem.inv_num  =  inv.inv_num 
)inv on inv.cust_Name = cust.cust_Name
Where cust.cust_seq = '0' and inv.cust_Num ='FER001S'
Group by cust.Cust_num,cust.name
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.06 seconds. Powered By: Snitz Forums 2000