MySQL Forums
Forum List  »  Newbie

How returning zero with COUNT() ?
Posted by: Domenico Mastronardi
Date: July 03, 2005 04:34AM

I have this table:

CREATE TABLE `people` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) NOT NULL default '',
`sex` char(1) NOT NULL default 'M',
`age` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM;

And I want knon how many Females and Males there are in three ranges of ages:
from 0 to 20, from 21 to 30 and over 31.
I've used COUNT() GROUP BY(sex) but the problem is that COUNT() doesn't return zero
but I want see the exact count of peoples of every ranges of age (for sex) including zero.

This is my query:

SELECT sex, ' < 20' AS range, COUNT(*) AS Num FROM people WHERE age between 0 and 20 GROUP BY(sex)
UNION
SELECT sex, '21-30' AS range, COUNT(*) AS Num FROM people WHERE age between 21 and 30 GROUP BY(sex)
UNION
SELECT sex, ' > 30' AS range, COUNT(*) AS Num FROM people WHERE age > 30 GROUP BY(sex)

How can I see zeros ?

Thanks in advance.

Options: ReplyQuote


Subject
Written By
Posted
How returning zero with COUNT() ?
July 03, 2005 04:34AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.