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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jeancaffou
Forum Newbie
Posts: 8
Joined: Wed Nov 26, 2008 3:57 pm

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

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post by VladSun »

[sql]SELECT     count(DATE),    MONTH(DATE),     YEAR(DATE)FROM     briskstatGROUP BY    MONTH(DATE),     YEAR(DATE)[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
jeancaffou
Forum Newbie
Posts: 8
Joined: Wed Nov 26, 2008 3:57 pm

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

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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 ... ? ;)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply