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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 approach to calculating two field
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

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,

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

many thanks


Flowing Fount of Yak Knowledge

7174 Posts

Posted - 12/13/2012 :  10:49:23  Show Profile  Reply with Quote
Select cust.Cust_num,,
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,
Go to Top of Page
  Previous Topic Topic Next 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.02 seconds. Powered By: Snitz Forums 2000