Page 1 of 1

SQL - select count() for each month of the year

Posted: Tue Jan 06, 2009 1:08 pm
by jeancaffou
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:

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
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.

Re: SQL - select count() for each month of the year

Posted: Tue Jan 06, 2009 1:23 pm
by VladSun
[sql]SELECT     count(DATE),    MONTH(DATE),     YEAR(DATE)FROM     briskstatGROUP BY    MONTH(DATE),     YEAR(DATE)[/sql]

Re: SQL - select count() for each month of the year

Posted: Tue Jan 06, 2009 6:52 pm
by jeancaffou
Thanks, that answered a few questions, but not all of them:

a) I created an index 'timestamps' and if I write USE INDEX(timestamps) it doesn't speed up the process, in fact, I think it slows it down quite a bit sometimes. I failed to see why.

b) How come count(*) in conjunction with GROUP BY returns the lenght of the subset of each group, and not the number of rows in the result?
For example, if I use

Code: Select all

SELECT
    count(DATE) as n,
FROM
    briskstat
GROUP BY
    DATE(DATE)
I would expect the number of distinct dates in the result... I know that this question seems illogical, I'm new to SQL and I know that getting the number of distinct dates would require, for example SELECT distinct date(`date`) from briskstat, but everytime I worked with count(), I got the number of rows, not anything else.

Re: SQL - select count() for each month of the year

Posted: Tue Jan 06, 2009 7:02 pm
by VladSun
For GROUP BY optimization - http://dev.mysql.com/doc/refman/5.0/en/ ... ation.html

For DISTINCT COUNT - http://dev.mysql.com/doc/refman/5.0/en/ ... t-distinct

Look again to your query ;) It will produce N rows with a single column with constant value = 1. And N is equal to ... ? ;)