Solved

MYSQL Select Statement to Return Groups of Ages?

Posted on 2004-08-16
2
749 Views
Last Modified: 2006-11-17
Can you help?  I have this working mySQL Select statement that returns a ONE ROW result ... that counts the number of users between the age of 18 and 25.

SELECT Count(*) AS CountOf18to25s
FROM opinionvotes o, users u,  demographics  d
WHERE o.userID = u.userID
AND u.userID = d.userID
AND o.opinion_id = 232
AND (d.birthyear + 1900) BETWEEN (Year(Now()) - 25) AND (Year(Now()) - 18)


I ALSO need to find out how many users are between these age bands ALSO:
26 to 35
36 to 45
46 to 55
56 to 65
>65


RATHER than performing 6 seperate SELECT statements ... ONE for EACH of these AGE bands (including the 18 to 25) ... I'm HOPING a Select Statement can be written that will return 6 ROWS ... with ONE ROW for EACH of the above age bands.  IS this possible?

Thanks!
Each 'record' in the demographics table contains a field called 'birthyear' which is the TWO DIGIT YEAR of BIRTH! SO I need to ADD 1900 to the YEAR i.e. 65 + 1900 = 1965 and SUBTRACT the 1965 from the current year (2004) to get 39 years of age.

Richard
0
Comment
Question by:rcbuchanan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 6

Accepted Solution

by:
andreif earned 500 total points
ID: 11815665
Hi, here is my solution

1. create extra table for age groups:

create table agegroups (startage int, endage int, title varchar(25));
and add all intervals there, like this:
18, 25, '18-25'
26, 35, '26-35'
...
66, 999, '>65'

2. now your select will look like this:

SELECT ag.title, Count(*)
FROM opinionvotes o, users u,  demographics  d
LEFT JOIN agegroups ag ON (year(now()) - d.birthyear - 1900 between ag.startage and ag.endage);
WHERE o.userID = u.userID
AND u.userID = d.userID
AND o.opinion_id = 232
GROUP BY ag.startage
0
 

Author Comment

by:rcbuchanan
ID: 11815859
wondeful! thanks!
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question