CODEWITHSUNDEEP
mysqlgroup-by
JD Isaacks
JD Isaacks

Reputation: 57964

MySQL: group by date RANGE?

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

Upvotes: 2

Views: 14814

Answers (3)

CAbbott
CAbbott

Reputation: 8098

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

Upvotes: 4

DRapp
DRapp

Reputation: 48139

look into the different DATE-based functions and build based on that, such as

select  YEAR( of your date ) + MONTH( of your date ) as ByYrMonth

but the result in above case would need to be converted to character to prevent a year of 2009 + January ( month 1) = 2010 also getting falsely grouped with 2008 + February (month 2 ) = 2010, etc... Your string should end up as something like:

...
200811
200812
200901
200902
200903
...

If you wanted by calendar Quarters, you would have to do a INTEGER of the (month -1) divided by 4 so...

Jan (-1) = 0 / 4 = 0
Feb (-1) = 1 / 4 = 0
Mar (-1) = 2 / 4 = 0
Apr (-1) = 3 / 4 = 0
May (-1) = 4 / 4 = 1
June (-1)= 5 / 4 = 1 ... etc...

Yes, a previous example explicitly reference the QUARTER() function that handles more nicely, but if also doing based on aging, such as 30, 60, 90 days, you could apply the similar math above but divide by 30 for your groups.

Upvotes: 0

Ryan Bair
Ryan Bair

Reputation: 2634

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

Upvotes: 2

Related Questions