CODEWITHSUNDEEP
mysqlsql
Danny
Danny

Reputation: 343

MySQL Convert String To Date Calculate Age And Order BY Age

I'm trying to write a mysql query that will convert a string to a date and then calculate an age and then count the ages and group by age.

I have the string to date and calculate age query:

SELECT YEAR(CURRENT_TIMESTAMP) - YEAR(STR_TO_DATE( birthday,  '%m/%d/%Y' )) - (RIGHT(CURRENT_TIMESTAMP, 5) < RIGHT(STR_TO_DATE( birthday,  '%m/%d/%Y' ), 5)) as age 
  FROM `persons`

But I can't count and group by age. Could anyone help me?

Upvotes: 1

Views: 727

Answers (1)

John Woo
John Woo

Reputation: 263693

SELECT  EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),STR_TO_DATE(birthday, '%m/%d/%Y'))))) + 0 age,
        COUNT(*) TotalCount
FROM    tableNAme
GROUP   BY EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),STR_TO_DATE(birthday, '%m/%d/%Y'))))) + 0
ORDER   BY TotalCount DESC

Upvotes: 1

Related Questions