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


/ Published in: MySQL
Save to your folder(s)



Copy this code and paste it in your HTML
  1. SELECT derived.usersid, derived.product_id, count( derived.user_id ) AS viewcount, SUM( IF( age <20, 1, 0 ) ) AS 'Under 20', SUM( IF( age
  2. AND 29 , 1, 0 ) ) AS '20 - 29', SUM( IF( age
  3. AND 39 , 1, 0 ) ) AS '30 - 39', SUM( IF( age
  4. AND 49 , 1, 0 ) ) AS '40 - 49', SUM( IF( age
  5. AND 59 , 1, 0 ) ) AS '50 - 59', SUM( IF( age
  6. AND 69 , 1, 0 ) ) AS '60 - 69', SUM( IF( age
  7. 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)'
  8.  
  9. SELECT iu.usersid, ipv.product_id, ipv.user_id, count( (
  10. ipv.user_id
  11. ) ) AS viewcount, (
  12. YEAR( CURRENT_DATE ) - YEAR( iu.dob )
  13. ) AS age
  14. FROM `product_views` AS ipv
  15. INNER JOIN users AS iu ON ipv.user_id = iu.usersid
  16. WHERE ipv.product_id =1
  17. GROUP BY ipv.user_id
  18. ) AS derived
  19. GROUP BY product_id
  20. LIMIT 0 , 30

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.