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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 sum cost from 3 different tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mikehjun
Starting Member

24 Posts

Posted - 03/11/2013 :  18:18:49  Show Profile  Reply with Quote
I have Material, Labor, Equipment tables. Each table has field for AccountNum, WorkorderID, Units, Cost.

I need to sum total cost of all three based on AccountNum.

each table looks like,

Material..
WO, Acct, Unit, Cost
1, 101, 1, 25
1, 101, 2, 20
1, 103, 1, 15
1, 103, 1, 10

Equipment and Labor also are similar.


SELECT
E.ACCTNUM, SUM(COST)
FROM [CLGT].[azteca].[EQUIPMENTCOSTACT] E
WHERE E.WORKORDERID = 98
GROUP BY E.ACCTNUM
UNION
SELECT
L.ACCTNUM, SUM(COST)
FROM [CLGT].[azteca].LABORCOSTACT L
WHERE L.WORKORDERID = 98
GROUP BY L.ACCTNUM
UNION
SELECT
M.ACCTNUM, SUM(COST)
FROM [CLGT].[azteca].MATERIALCOSTACT M
WHERE M.WORKORDERID = 98
GROUP BY M.ACCTNUM

After I run this, I got the result like,

ACCTNum COST
101-85-0732 0.00
101-85-0733 13.32
101-85-0733 98.00
101-85-0733 697.18
111-85-0811 162.26

I'd like to have the final table looks like,
101-85-0732 0.00
101-85-0733 808.50
111-85-0811 162.26

Edited by - Mikehjun on 03/11/2013 18:22:14

LoztInSpace
Aged Yak Warrior

939 Posts

Posted - 03/11/2013 :  21:00:04  Show Profile  Reply with Quote

select
acctnum, sum(cost)
from
(
SELECT
E.ACCTNUM, COST
FROM [CLGT].[azteca].[EQUIPMENTCOSTACT] E
WHERE E.WORKORDERID = 98
UNION all
SELECT
L.ACCTNUM, COST
FROM [CLGT].[azteca].LABORCOSTACT L
WHERE L.WORKORDERID = 98
UNION all
SELECT
M.ACCTNUM, COST
FROM [CLGT].[azteca].MATERIALCOSTACT M
WHERE M.WORKORDERID = 98
) x
group by ACCTNUM

Note use of UNION ALL.
If you use just UNION you will drop out identical rows.
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.22 seconds. Powered By: Snitz Forums 2000