| Author |
 Topic  |
|
|
Mikehjun
Starting Member
22 Posts |
 Posted - 03/11/2013 : 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
|
Edited by - Mikehjun on 03/11/2013 18:22:14
|
|
|
LoztInSpace
Aged Yak Warrior
876 Posts |
Posted - 03/11/2013 : 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. |
 |
|
| |
Topic |
|
|
|
sum cost from 3 different tables
