Advanced Search

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

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

Expand | Embed | Plain Text

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. BETWEEN 20
  3. AND 29 , 1, 0 ) ) AS '20 - 29', SUM( IF( age
  4. BETWEEN 30
  5. AND 39 , 1, 0 ) ) AS '30 - 39', SUM( IF( age
  6. BETWEEN 40
  7. AND 49 , 1, 0 ) ) AS '40 - 49', SUM( IF( age
  8. BETWEEN 50
  9. AND 59 , 1, 0 ) ) AS '50 - 59', SUM( IF( age
  10. BETWEEN 60
  11. AND 69 , 1, 0 ) ) AS '60 - 69', SUM( IF( age
  12. BETWEEN 70
  13. 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)'
  14. FROM (
  15.  
  16. SELECT iu.usersid, ipv.product_id, ipv.user_id, count( (
  17. ipv.user_id
  18. ) ) AS viewcount, (
  19. YEAR( CURRENT_DATE ) - YEAR( iu.dob )
  20. ) AS age
  21. FROM `product_views` AS ipv
  22. INNER JOIN users AS iu ON ipv.user_id = iu.usersid
  23. WHERE ipv.product_id =1
  24. GROUP BY ipv.user_id
  25. ) AS derived
  26. GROUP BY product_id
  27. LIMIT 0 , 30

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.