SQL - select count() for each month of the year
Posted: Tue Jan 06, 2009 1:08 pm
Hi,
I have a fairly large table, used for statistics, where each record is a result from a game. I have a coulmn datetime, named date, and now I'd like to SELECT from this table to have a count(date) for each month of the year.
For now, my query looks like this:
I'd like to know if there is a faster and better way to do this, because the query now takes 60 seconds to execute.
I have a fairly large table, used for statistics, where each record is a result from a game. I have a coulmn datetime, named date, and now I'd like to SELECT from this table to have a count(date) for each month of the year.
For now, my query looks like this:
Code: Select all
SELECT count(DATE) as MONTH FROM briskstat WHERE MONTH(DATE) = 1 AND YEAR(DATE) = 2008 UNION
SELECT count(DATE) FROM briskstat WHERE MONTH(DATE) = 2 AND YEAR(DATE) = 2008 UNION
SELECT count(DATE) FROM briskstat WHERE MONTH(DATE) = 3 AND YEAR(DATE) = 2008 UNION
SELECT count(DATE) FROM briskstat WHERE MONTH(DATE) = 4 AND YEAR(DATE) = 2008 UNION
SELECT count(DATE) FROM briskstat WHERE MONTH(DATE) = 5 AND YEAR(DATE) = 2008 UNION
SELECT count(DATE) FROM briskstat WHERE MONTH(DATE) = 6 AND YEAR(DATE) = 2008 UNION
SELECT count(DATE) FROM briskstat WHERE MONTH(DATE) = 7 AND YEAR(DATE) = 2008 UNION
SELECT count(DATE) FROM briskstat WHERE MONTH(DATE) = 8 AND YEAR(DATE) = 2008 UNION
SELECT count(DATE) FROM briskstat WHERE MONTH(DATE) = 9 AND YEAR(DATE) = 2008 UNION
SELECT count(DATE) FROM briskstat WHERE MONTH(DATE) = 10 AND YEAR(DATE) = 2008 UNION
SELECT count(DATE) FROM briskstat WHERE MONTH(DATE) = 11 AND YEAR(DATE) = 2008 UNION
SELECT count(DATE) FROM briskstat WHERE MONTH(DATE) = 12 AND YEAR(DATE) = 2008