MySQL query using alias to count certain years from DATETIME I have a table which consists of an ID count, a DATETIME field named orderDate showing when a customer bought an item. I am trying to create a query which takes ID and orderDate, to find all dates which were ordered in 2015 using an alias and count them.

For example, my output table should contain IDs and a count of all the orders placed in 2015 for that ID in a single row.

My query, which I have been using to try make it work with very little success is:

SELECT ID,YEAR(orderDate) as lastyear 
from t1 
HAVING lastyear = 2015


I am aware that I cannot use WHERE, so it's a case of using HAVING, but I cannot for the life of me right now figure out how to use the alias to count.

I am using MySQL 5.1.
This query will work for any given year based on today

SET group_concat_max_len = 1024 * 1024 * 100;
SELECT ID,GROUP_CONCAT(orderDate ORDER BY orderDate) orderDates,COUNT(1) orderCount
FROM t1
WHERE orderDate &gt;= MAKEDATE(YEAR(NOW()) - 1,1)
AND   orderDate &lt;= MAKEDATE(YEAR(NOW()),1) - INTERVAL 1 DAY;
GROUP BY ID;


Running this query now will give you all of 2015.

Running this query next year will give you all of 2016.

NOTES

MAKEDATE(YEAR(NOW()) - 1,1) gives you the first day of last year

MAKEDATE(YEAR(NOW()),1) - INTERVAL 1 DAY gives you the last day of last year

The reason I use

SET group_concat_max_len = 1024 * 1024 * 100;


The default for group_concat_max_len is 1024 (1K). I set it to 100M in case the list is long.

This query will work faster if you have a comound index on (ID,orderDate)

If you do not have such an index, then create one:

ALTER TABLE t1 ADD INDEX ID_orderDate_ndx (ID,orderDate);


COMMENTARY ON YOUR QUESTION

With regard to your original query


You cannot use HAVING without a GROUP BY in the strictest sense (See MySQL Documentation on MySQL Handling of GROUP BY)
There is no need for HAVING


HAVING is the same as a WHERE except it is against an aggregated set of data.
Using WHERE just gets the data before aggregation

Using HAVING complicates the query because you would have to structure the query for counting multiple years and then select just 2015. Using WHERE gets data only from 2015. Then, you can aggregate by ID.