Page 1 of 1

MySQL showing zero on a sum

Posted: Wed Apr 29, 2009 6:14 am
by Nunners
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.

Code: Select all

SELECT sum(amount) 
FROM invoices 
WHERE nominalCode IN (4000,4001,4002,4003,4004,4005) 
GROUP BY nominalCode 
ORDER BY nominalCode
That works fine, but doesn't show any zero values (or null don't mind which)

So 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.nominalCode
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

Re: MySQL showing zero on a sum

Posted: Wed Apr 29, 2009 10:41 am
by dom_scott
Hi, probably you sorted that already by now, but I don't see anything wrong about your SQL.
The first code should return you a value of 0 when the amounts of that nominal code sum 0. When the amounts of a nominal code are all null then the sum should be null as well...
There must be something else, could you give us a small sample of your table?

Re: MySQL showing zero on a sum

Posted: Thu Apr 30, 2009 7:09 am
by Nunners
Nope haven't sorted it... still trying to work out what I've got wrong!

Here's the table setup:

Code: Select all

 
CREATE TABLE IF NOT EXISTS `nominalCodes` (
  `nominalCode` int(4) NOT NULL DEFAULT '0',
  `name` varchar(40) NOT NULL DEFAULT '',
  PRIMARY KEY  (`nominalCode`),
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Code: Select all

 
CREATE TABLE IF NOT EXISTS `invoices` (
  `invoiceId` int(10) NOT NULL AUTO_INCREMENT,
  `nominalCode` int(4) NOT NULL DEFAULT '0',
  `invoiceDate` date NOT NULL DEFAULT '0000-00-00',
  `details` varchar(150) NOT NULL,
  `amount` decimal(8,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY  (`invoiceId`),
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6012 ;
On both tables, I haven't included everything, but the above is the relevant fields.

Again, anyone any thoughts?

Cheers
Nunners

Re: MySQL showing zero on a sum

Posted: Thu Apr 30, 2009 10:18 am
by dom_scott
Got this table:

Code: Select all

 id/nominalCode/date/amount
1   4002    0000-00-00      0
2   4003    0000-00-00      2
3   4002    0000-00-00      0
4   4003    0000-00-00      1
then run

Code: Select all

select sum(amount), nominalCode from invoices group by nominalCode
order by nominalCode
and get

Code: Select all

0      4002
3      4003
As you can see, the total for 4002 = 0, isn't this what you're trying to get?

Re: MySQL showing zero on a sum

Posted: Mon May 04, 2009 9:42 am
by Bill H
I'm not sure if I"m following. You're saying it doesn't show zero amounts or NULL's, but they don't contribute to a sum, both would leave a sum at zero, so how and why would it be "showing" them? If you are seeking to know how many invoices there are you need the COUNT() function call rather than SUM().