CODEWITHSUNDEEP
sql
KumarG
KumarG

Reputation: 1

Sum not selecting the values with Zero

I have two tables CDmachine and trnasaction.

CDMachine Table with columns CDMachineID, CDMachineName, InstallationDate

Transaction table with columns TransactionID,CDMachineID,TransactionTime,Amount

I am calculating revenue using the below query but it eliminates the machine without any transaction

SELECT CDMachine.MachineName,
       SUM(Transaction.Amount)
FROM CDMachine
LEFT JOIN TRANSACTION ON CDMachine.CDMachineID = Transaction.CDMachineID
WHERE Transaction.TransactionTime BETWEEN '2019-01-01' AND '2019-01-31'
GROUP BY CDMachine.CDMachineName
ORDER BY 2

Upvotes: 0

Views: 39

Answers (2)

Dave Costa
Dave Costa

Reputation: 48111

Even though you are using a LEFT JOIN, the fact that you have a filter on a column from the joined table causes rows that don't meet the join condition to be removed from the result set.

You need to apply the filter on transaction time to the transactions table, before joining it or as part of the join condition. I would do it like this:

SELECT CDMachine.MachineName,
       SUM(Transaction.Amount)
FROM CDMachine
LEFT JOIN (
  SELECT * FROM TRANSACTION 
  WHERE Transaction.TransactionTime BETWEEN '2019-01-01' AND '2019-01-31'
) AS Transaction
ON CDMachine.CDMachineID = Transaction.CDMachineID
GROUP BY CDMachine.CDMachineName
ORDER BY 2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269663

Move the WHERE condition to the ON clause:

select m.MachineName, sum(t.Amount)
from CDMachine m left join
     Transaction t
     on m.CDMachineID = t.CDMachineID and
        t.TransactionTime between '2019-01-01' and '2019-01-31'
group by m.CDMachineName
order by 2;

The WHERE clause turns the outer join to an inner join -- meaning that you are losing the values that do not match.

If you want 0 rather than NULL for the sum, then use:

select m.MachineName, coalesce(sum(t.Amount), 0)

Upvotes: 1

Related Questions