MySQL fetch records based on different age limits like(20-30,30-40)etc.,

Revision: 49739

at July 29, 2011 02:57 by erraja_07

Initial Code

SELECT derived.usersid, derived.product_id, count( derived.user_id ) AS viewcount, SUM( IF( age <20, 1, 0 ) ) AS 'Under 20', SUM( IF( age
BETWEEN 20
AND 29 , 1, 0 ) ) AS '20 - 29', SUM( IF( age
BETWEEN 30
AND 39 , 1, 0 ) ) AS '30 - 39', SUM( IF( age
BETWEEN 40
AND 49 , 1, 0 ) ) AS '40 - 49', SUM( IF( age
BETWEEN 50
AND 59 , 1, 0 ) ) AS '50 - 59', SUM( IF( age
BETWEEN 60
AND 69 , 1, 0 ) ) AS '60 - 69', SUM( IF( age
BETWEEN 70
AND 79 , 1, 0 ) ) AS '70 - 79', SUM( IF( age >=80, 1, 0 ) ) AS 'Over 80', SUM( IF( age IS NULL , 1, 0 ) ) AS 'Not Filled In (NULL)'
FROM (

SELECT iu.usersid, ipv.product_id, ipv.user_id, count( (
ipv.user_id
) ) AS viewcount, (
YEAR( CURRENT_DATE ) - YEAR( iu.dob )
) AS age
FROM `product_views` AS ipv
INNER JOIN users AS iu ON ipv.user_id = iu.usersid
WHERE ipv.product_id =1
GROUP BY ipv.user_id
) AS derived
GROUP BY product_id
LIMIT 0 , 30

Initial URL

Initial Description

Initial Title

MySQL fetch records based on different age limits like(20-30,30-40)etc.,

Initial Tags

Initial Language

MySQL

Choose a language for easy browsing: