using a count function

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=gcdmg-mysql-2@m.gmane.org

Re: using a count function

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=mos99@fastmail.fm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org