UNION OF 2 TABLES - CAN'T GET SUM

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

UNION OF 2 TABLES - CAN'T GET SUM

Author

Topic

sadie
Starting Member

1 Post

Posted - 2005-09-07 : 21:18:56

I am trying to get a consolidated sum of all the columns of the two tables that I am Using the UNION on. I can not get the sum function to work or the group by.

SELECT T2.State,t2.Taxcode,
Taxable =
Case
When T1.TaxCode <> 'exempt' and T1.TaxStatus = 'Y' then T1.LineTotal - ((T0.DiscPrcnt/100) * T1.LineTotal)
Else 0
End,
'NonTaxable' =
Case
When T1.TaxCode = 'exempt' or T1.TaxStatus = 'N' then T1.LineTotal + T1.DistribSum - ((T0.DiscPrcnt/100) * T1.LineTotal)
Else T1.DistribSum
End,
T1.VatSum as 'Total Tax', (T1.LineTotal + T1.DistribSum - ((T0.DiscPrcnt/100) * T1.LineTotal) + T1.Vatsum) as 'Line Total'
FROM inv1 t1 inner JOIN oinv t0 ON T0.DocEntry = T1.DocEntry inner join CRD1 T2 on T0.Cardcode = T2.CardCode
WHERE T0.DocDate >='[%1]' AND T0.DocDate <='[%2]' and t2.address = T0.shiptocode and t2.adrestype = 's'
UNION ALL
SELECT t2.state,t2.taxcode,
Taxable =
Case
When T1.TaxCode <> 'exempt' and T1.TaxStatus = 'Y' then -(T1.LineTotal - ((T0.DiscPrcnt/100) * T1.LineTotal))
Else 0
End,
'NonTaxable' =
Case
When T1.TaxCode = 'exempt' or T1.TaxStatus = 'N' then -(T1.LineTotal + T1.DistribSum - ((T0.DiscPrcnt/100) * T1.LineTotal))
Else T1.DistribSum
End,
-T1.VatSum as 'Total Tax', -(T1.LineTotal + T1.DistribSum - ((T0.DiscPrcnt/100) * T1.LineTotal) + T1.Vatsum) as 'Line Total'
FROM RIN1 t1 inner JOIN ORIN t0 ON T0.DocEntry = T1.DocEntry inner join CRD1 T2 on T0.Cardcode = T2.CardCode
WHERE T0.DocDate >='[%1]' AND T0.DocDate <='[%2]' and t2.address = T0.shiptocode and t2.adrestype = 's'
order by T2.STATE

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-07 : 22:58:10

what's the error? where do you expect the sum function to appear, where do you need the group by?

are you tyring to sum the columns of table1 with table2?

use subquery for the result like

select sum (col1),sum(col2),sum(col3) from
(your union query here) total

HTH

--------------------
keeping it simple...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-08 : 21:13:44

you can't use a group by on your second query for t2.state only,
you should include all the columns that are not aggregated like t2.code and taxable

also, don't use order by inside the union, sort them in the last query, that would be your last select on the subquery

if we follow your original code you'll get


select state,taxcode,sum(taxable) as 'Taxable' from
(
SELECT T2.State,t2.Taxcode,
Taxable = 
Case
When T1.TaxCode <> 'exempt' and T1.TaxStatus = 'Y' then T1.LineTotal - ((T0.DiscPrcnt/100) * T1.LineTotal)
Else 0
End,
'NonTaxable' = 
Case
When T1.TaxCode = 'exempt' or T1.TaxStatus = 'N' then T1.LineTotal + T1.DistribSum - ((T0.DiscPrcnt/100) * T1.LineTotal)
Else T1.DistribSum
End, 
T1.VatSum as 'Total Tax', (T1.LineTotal + T1.DistribSum - ((T0.DiscPrcnt/100) * T1.LineTotal) + T1.Vatsum) as 'Line Total'
FROM inv1 t1 inner JOIN oinv t0 ON T0.DocEntry = T1.DocEntry inner join CRD1 T2 on T0.Cardcode = T2.CardCode 
WHERE T0.DocDate >='[%1]' AND T0.DocDate <='[%2]' and t2.address = T0.shiptocode and t2.adrestype = 's' 
UNION ALL
SELECT t2.state,t2.taxcode,
Taxable = 
Case
When T1.TaxCode <> 'exempt' and T1.TaxStatus = 'Y' then -(T1.LineTotal - ((T0.DiscPrcnt/100) * T1.LineTotal))
Else 0
End,
'NonTaxable' = 
Case
When T1.TaxCode = 'exempt' or T1.TaxStatus = 'N' then -(T1.LineTotal + T1.DistribSum - ((T0.DiscPrcnt/100) * T1.LineTotal))
Else T1.DistribSum
End, 
-T1.VatSum as 'Total Tax', -(T1.LineTotal + T1.DistribSum - ((T0.DiscPrcnt/100) * T1.LineTotal) + T1.Vatsum) as 'Line Total'
FROM RIN1 t1 inner JOIN ORIN t0 ON T0.DocEntry = T1.DocEntry inner join CRD1 T2 on T0.Cardcode = T2.CardCode 
WHERE T0.DocDate >='[%1]' AND T0.DocDate <='[%2]' and t2.address = T0.shiptocode and t2.adrestype = 's'
order by T2.STATE) total
group by state,taxcode
order by state

just added the one in blue text and without the ddl and some dml, I can't test this, anyways, hope this helps...

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources