I'm struggling with how to phrase my problem, sorry for any confusion. I have 3 tables associated with each purchase: Transaction; TransactionEntry, which includes separate rows for each item in the transaction; and TaxEntry, which includes the the taxID and taxAmount for each entry in TransactionEntry.
I want to find the total, in a given month, for each TaxAmount AND price by TaxID. Price and Date are found in Transaction Entry, while TaxAmount and TaxID are found in TaxEntry. In addition, we have multiple taxes per item (state and county).
My tables look something like this:
Transaction Entry:
TransactionNum Price Date
1 8.99 2015-01-06
1 3.65 2015-01-06
2 3.99 2015-03-06
TaxEntry:
TransactionNum TaxID TaxAmount
1 2 0.89
1 16 0.09
1 2 0.37
1 16 0.04
2 4 0.40
2 16 0.04
I want to find the sum of Price, by TaxID, for a given month. So, for example, I want 8.99+3.65 to be returned for TaxID 2; 3.99 for TaxID 4, and 8.99+3.65+3.99 for TaxID 16. Everything I try returns the incorrect amounts, repeating each number multiple times.
My code is:
SELECT SUM(Price), TaxID
FROM TaxEntry XE
JOIN TransactionEntry TE
ON XE.TransactionNumber = TE.TransactionNumber
WHERE CAST(TE.TransactionTime AS DATE) BETWEEN '06-01-2015' and '06-30-2015'
GROUP BY XE.TaxID
My results are in the range of up to three times too large.
I have tried it with joins of various types but it still repeats info. I feel like I should be doing something with DISTINCT but it fails to accomplish anything.
You will get the right result if you pre-aggregate the tax table:
Notice:
group by
.