Chris,
You are using Count when you should be using Sum. Here is a solution you can try:


SELECT
SUM(IF(DATEDIFF(dateofinterview,dateofbirth)/365.25 BETWEEN 0 AND 18.999, 1,0)) AS "18 and Under", SUM(IF(DATEDIFF(dateofinterview,dateofbirth)/365.25 BETWEEN 19 AND 65.999, 1,0)) AS "19-65", SUM(IF(DATEDIFF(dateofinterview,dateofbirth)/365.25 >= 66, 1,0)) AS "Over 65"
 FROM demographics;

I noticed from your title you say "Under 18" and then "19-65". I assume you are including 18 year olds for "Under 18"?

Mike


At 03:41 PM 5/29/2010, Chris Elhardt wrote:
This is probably pretty obvious to everyone except me.

I have a couple of columns, DateOfInterview and DateOfBirth in a
table named Demographics

For a monthly report I have a script where the operator enters the
start and end dates of the reporting period.  I need a query result
with single line  of three columns, each with a count of the number
of interviews for that reporting period:


||  Under 18   ||        19-65   ||     over 65  ||
||        5            ||         19        ||       23         ||


I've made three queries to select the counts for each age range, then
used them to form another query I thought would give me an acceptable
output.

This gives me multiple lines, all with the same numbers:


SELECT Count([Under 18 count].[Under 18]) AS [CountOfUnder 18], Count ([19 to 65 count].[19 to 65]) AS [CountOf19 to 65], Count([Over 65
count].Over65) AS CountOfOver65
FROM [Under 18 count], [19 to 65 count], [Over 65 count], Demographics
WHERE (((Demographics.[Date of Interview]) Between [Report Start
Date] And [Report End Date]));



||  Under 18   ||        19-65   ||     over 65  ||
||        5            ||         19        ||       23         ||
||        5            ||         19        ||       23         ||
||        5            ||         19        ||       23         ||


Like I said, this should be pretty obvious to everyone but me.

chris.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=arch...@jab.org

Reply via email to