approach to calculating two field

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

approach to calculating two field

Author

Topic

dave1816
Starting Member

9 Posts

Posted - 2012-12-13 : 10:29:52

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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-13 : 10:49:23

[code]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[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources