CODEWITHSUNDEEP
mysql
Junior Leiva
Junior Leiva

Reputation: 13

Age from MySQL with filter

I need count persons with age > 60, but MySQL return 0. Please, help me.

SELECT
   count(*),
   TIMESTAMPDIFF( YEAR, birth_date, CURDATE( ) ) AS age
FROM
   voluntario v
WHERE 
   v.id_status = 1
   HAVING age >= 60

Thanks!

Upvotes: 1

Views: 94

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

The reason that your question doesn't work is because it is an aggregation query. Hence the having is applied after the aggregation. To filter before the aggregation, use a where clause.

That is easy enough to do:

SELECT count(*)
FROM voluntario v
WHERE v.id_status = 1 AND
      v.birth_date < date_sub(CURDATE(), interval 60 year)

Note that the date arithmetic is performed on CURDATE() rather than on birth_date. This allows an index containing birth_date to be used for the query (if appropriate).

Having age in the SELECT doesn't make sense, because there are (potentially) many different ages, and only one indeterminate value is returned by the query.

Upvotes: 1

xsami
xsami

Reputation: 1330

Why don't you do this:

SELECT
   count(*),
   birth_date
FROM
   voluntario v
WHERE 
   v.id_status = 1 AND TIMESTAMPDIFF( YEAR, birth_date, CURDATE( ) ) >= 60

Upvotes: 0

Related Questions