MySQL showing zero on a sum
Posted: Wed Apr 29, 2009 6:14 am
I'm unfortunately having to create a report in Excel which uses data from a MySQL table - don't ask it's my boss! I've got the mysql connector working fine, but am now trying to create the various queries to the database I have - which is where I've hit the problem....
From a table of invoices and nominalCodes, I want to be abel to get the total amount of invoices for each nominal code, even if the total is zero.
That works fine, but doesn't show any zero values (or null don't mind which)
So I tried a left join:
I thought because all the nominalCodes exist it would show the correct output, but again, doesn't show null/zero values.
Any thoughts?
Thanks
Nunners
From a table of invoices and nominalCodes, I want to be abel to get the total amount of invoices for each nominal code, even if the total is zero.
Code: Select all
SELECT sum(amount)
FROM invoices
WHERE nominalCode IN (4000,4001,4002,4003,4004,4005)
GROUP BY nominalCode
ORDER BY nominalCodeSo I tried a left join:
Code: Select all
SELECT sum(amount) as total
FROM nominalCodes
LEFT JOIN invoices ON invoices.nominalCode=nominalCodes.nominalCode
WHERE nominalCodes.nominalCode IN (4000,4001,4002,4003,4004,4005)
GROUP BY nominalCodes.nominalCode
ORDER BY nominalCodes.nominalCodeAny thoughts?
Thanks
Nunners