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
