Entries in my SQL Query doubles the result values

Question

I have 3 Tables (Transaction, TransactionEntry and TenderEntry). When I run my SQL query, I got the sum values twice because of TransactionEntry Table.

So let's say I have 2 Items (Clothe worth $288.75 and Free Umbrella worth $50.00). I settled the payment using Cash worth $300 and GC worth $100, now I have a Cash change worth $61.25.

Here are the entries in the 3 tables

Transaction table

TransactionNumber|Total|
========================
1                |338.75|

TransactionEntry Table

TransactionNumber|ItemID|Price|
===============================
1                |245648|288.75|
1                |129   |50.00 |

Tender Entry Table

TransactionNumber|TenderID|Description|Amount|
==============================================
1                |1       |Cash       |300.00| 
1                |1       |Cash       |-61.25|
1                |20      |GC         |100.00|

Here is what I made so far

select [Transaction].TransactionNumber,
       (case when [Transaction].RecallType = 0 then [Transaction].total else 0 end) as Sales,
       sum(case when TransactionEntry.ItemID = 6922 then TransactionEntry.Price
        when TransactionEntry.price = 0 then TransactionEntry.price else 0 end) as Free,
       sum(case when TenderEntry.tenderID = 1 then TenderEntry.Amount else 0 end) as Cash,
       sum(case when TenderEntry.tenderID = 20 then TenderEntry.Amount else 0 end) as GC
 from  [Transaction] inner join 
       TransactionEntry on [Transaction].transactionnumber = TransactionEntry.transactionnumber inner join 
       TenderEntry on [Transaction].transactionnumber = TenderEntry.transactionnumber
 group by [Transaction].TransactionNumber,
          (case when [Transaction].RecallType = 0 then [Transaction].total else 0 end)

I get this kind of output

TransactionNumber|Sales |Free  |Cash   |GC   |
==============================================
1                |338.75|150.00|477.50|200.00|

Instead of this

TransactionNumber|Sales |Free |Cash   |GC    |
==============================================
1                |338.75|50.00|238.75 |100.00|

Answer 1

You're wanting one row per transaction, so I'd suggest you sum your joined tables to the transaction level before doing the joins as follows:

select t1.TransactionNumber,
       case when t1.RecallType = 0 then t1.total else 0 end as Sales,
       t2.Free,
       t3.Cash,
       t3.GC
from [Transaction] t1
inner join (select transactionnumber, 
            sum(case when ItemID = 6922 then Price else 0 end) as free 
            from TransactionEntry 
            group by transactionnumber) as t2 
            on t1.transactionnumber = t2.transactionnumber
inner join (select transactionnumber, 
            sum(case when tenderID = 1 then Amount else 0 end) as Cash,
            sum(case when tenderID = 20 then Amount else 0 end) as GC
            from TenderEntry
            group by transactionnumber) as t3
            on t1.transactionnumber = t3.transactionnumber

Note that I've used table aliases (t1, t2, t3) to condense the code and make it a bit easier to read. It looks as though "Transaction" is a reserved word, so probably not a great choice of table name as it means you always have to use the square brackets to enclose the name when referring to it.

There seems to be an issue with this piece of your code,

sum(case when TransactionEntry.ItemID = 6922 then TransactionEntry.Price  
when TransactionEntry.price = 0 then TransactionEntry.price else 0 end) as Free,

because your Umbrella, which you say is Free, is item 129 not 6922. Also the "when TransactionEntry.price = 0 then ..." is setting the price to 0 regardless of the price. I'm not sure what you intended there?