sum cost from 3 different tables

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

SQL Server 2012 Forums

Transact-SQL (2012)

sum cost from 3 different tables

Author

Topic

Mikehjun
Starting Member

24 Posts

Posted - 2013-03-11 : 18:18:49

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

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-03-11 : 21:00:04

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources