CODEWITHSUNDEEP
mysql
php_nub_qq
php_nub_qq

Reputation: 16045

How to re-use function result in same query

I have the following query:

SELECT COUNT(id) FROM users WHERE 
DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), birthday)), "%Y") >17 AND 
DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), birthday)), "%Y") <22

Which is supposed to return the number of people between 18 and 21, and it does. The problem is that I don't like how I'm running the same set of functions twice. How do I avoid that?

Upvotes: 0

Views: 73

Answers (2)

Wolph
Wolph

Reputation: 80031

In this specific case you could use the BETWEEN operator, but alternatively you could also use a subquery for the more general case.

SELECT COUNT(id)
FROM users
WHERE DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), birthday)), "%Y") BETWEEN 18 and 21;

The subquery version:

SELECT COUNT(id)
FROM (
  SELECT
    id,
    DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), birthday)), "%Y") days_from_birthday
  FROM users
) as sub

Upvotes: 0

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324650

There's a magical operator in MySQL called BETWEEN. Observe:

SELECT COUNT(`id`) FROM `users`
  WHERE DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(),`birthday`)),"%Y")
      BETWEEN 18 and 21;

Note that the values to be used are inclusive, not exclusive.

Also note that you can do it more simply:

WHERE DATE_ADD(`birthday`,INTERVAL 18 YEAR)
          BETWEEN NOW() AND DATE_ADD(NOW(),INTERVAL 3 YEAR)

Upvotes: 3

Related Questions