MySQL: group by date RANGE?

Question

OK I have this query that groups 2 columns together quite nicely:

SELECT search_query_keyword, search_query_date, COUNT(1) as count
            FROM search_queries 
            WHERE search_query_date >= '.$from.' AND search_query_date <= '.$to.'
            GROUP BY search_query_keyword, search_query_date
            ORDER BY count DESC
            LIMIT 10

But what if I want to group by a date RANGE instead of just a date? Is there a way to do that?

Thanks!

EDIT: OK these answers are pretty complicated and I think what I want can be acheived a lot easier so let me re-explain. I want to select keywords over a time period ">= 20090601 AND <= 20090604" for example. But instead of getting repeated keywords I would rather just get the keyword ounce and how many times it occured. So for example instead of this:

keyword: foo
keyword: foo
keyword: foo
keyword: bar
keyword: bar

I would get:

keyword: foo, count: 3
keyword: bar, count: 2

Solution 1

I'm not exactly sure about the date range grouping -- you'd have to define the date ranging that you would want and then maybe you could UNION those queries:

SELECT 
    'Range 1' AS 'date_range',
    search_query_keyword
FROM search_queries
WHERE search_query_date >= '.$fromRange1.' AND search_query_date <= '.$toRange1.'
UNION
SELECT 
    'Range 2' AS 'date_range',
    search_query_keyword
FROM search_queries
WHERE search_query_date >= '.$fromRange2.' AND search_query_date <= '.$toRange2.'
GROUP BY 1,2

Or if you wanted to put them within a grouping of how many days old like "30 days, 60 days, etc" you could do this:

SELECT 
    (DATEDIFF(search_query_date, NOW()) / 30) AS date_group,
    search_query_keyword
FROM search_queries
GROUP BY date_group, search_query_keyword

EDIT: Based on the further information you provided, this query should produce what you want:

SELECT 
    search_query_keyword,
    COUNT(search_query_keyword) AS keyword_count
FROM search_queries
WHERE search_query_date >= '.$from.' AND search_query_date <= '.$to.'
GROUP BY search_query_keyword

Solution 2

You could group on a CASE statement or on the result of a function. For instance:

SELECT search_query_keyword, QUARTER(search_query_date), COUNT(1) as count
FROM search_queries 
WHERE search_query_date >= '.$from.' AND search_query_date <= '.$to.'
GROUP BY search_query_keyword, QUARTER(search_query_date)
ORDER BY count DESC

Comments

[JBCP]

Is there a way to modify the last query to span multiple search_query_date ranges? In this case, the returned columns would be (keyword, column, week) and you could run the query to return results from the last 6 weeks?

[aland]

"Returns the quarter of the year for date, in the range 1 to 4." - dev.mysql.com/doc/refman/5.5/en/…