CODEWITHSUNDEEP
mysqlwhere-clause
Pablo
Pablo

Reputation: 537

Using WHERE with AS in MySQL

I am using the following MySql query, and it does't work: 

SELECT YEAR(DATE) AS YEAR,
       MONTH(DATE) AS MONTH ,
       CONCAT(MONTH(DATE) , '-', YEAR(DATE)) AS monthYear,
       SUM(`sc-bytes` /1073741824) AS volume
FROM `r-by5minutes`
WHERE `ra-id` = '10000'
  AND `YEAR` = '2014'
  AND `MONTH` = '4'

Can't I combine WHERE with AS?

Upvotes: 0

Views: 218

Answers (3)

Simon at The Access Group
Simon at The Access Group

Reputation: 7027

In order to hit a decent index on the date column a better approach would be to take a different view. You are looking for any rows with a DATE that falls within April 2014, and so if you limit your DATE column based off the date needing to be between the first and last day in April you will allow a more efficient query to return the desired results, i.e.

SELECT YEAR(DATE) AS YEAR,
       MONTH(DATE) AS MONTH ,
       CONCAT(MONTH(DATE) , '-', YEAR(DATE)) AS monthYear,
       SUM(`sc-bytes` /1073741824) AS volume
FROM `r-by5minutes`
WHERE `ra-id` = '10000'
  AND `DATE` >= DATE('2014-04-01') -- Midnight on the first of this month
  AND `DATE` < DATE_ADD(DATE('2014-04-01'),INTERVAL 1 MONTH) -- Midnight on the first of next month

This takes in an argument which is the first day of the month you are looking for, and then uses the native DATE_ADD method to find the upper limit. This will allow matching of all dates from 00:00:00 on 2014-04-01 to 23:59:59 on 2014-04-30

Upvotes: 1

zessx
zessx

Reputation: 68790

WHERE clause is executed before SELECT, so you can't use aliases.

Option #1 : use a subquery

SELECT YEAR, 
       MONTH, 
       CONCAT(MONTH(DATE) , '-', YEAR(DATE)) AS monthYear, 
       SUM(`sc-bytes` /1073741824) AS volume
FROM (
    SELECT YEAR(DATE) AS YEAR,
           MONTH(DATE) AS MONTH,
           `sc-bytes`
    FROM `r-by5minutes`
    WHERE `ra-id` = '10000'
) AS sq
WHERE `YEAR` = '2014'
  AND `MONTH` = '4'

Option #2 : use HAVING clause

SELECT YEAR(DATE) AS YEAR,
       MONTH(DATE) AS MONTH ,
       CONCAT(MONTH(DATE) , '-', YEAR(DATE)) AS monthYear,
       SUM(`sc-bytes` /1073741824) AS volume
FROM `r-by5minutes`
WHERE `ra-id` = '10000'
HAVING `YEAR` = '2014'
   AND `MONTH` = '4'

Upvotes: 0

user3030212
user3030212

Reputation: 439

You cannot use alias in WHERE clause, e.g.

WHERE YEAR(DATE) = 2014 AND MONTH(DATE) = 4

Upvotes: 0

Related Questions