Re: aggregate count and group by

[Jim Grill]

Jim Grill <[EMAIL PROTECTED]> wrote on 02/18/2005 03:17:39 PM:

Hi,
I need some SQL guru help on this one.
I'm trying to re factor an existing application where a number of clicks 

grouped by keyword for two different time periods are needed.  For 
example, a user picks a date range and the application selects a count 
for how many times a keyword appears in a table for that date range. 
example:

mysql> SELECT keyword, COUNT(id) AS clicks FROM clicksTable WHERE 
domain = 'mydomain.com' AND click_date BETWEEN '2004-12-01' AND 
'2004-12-15' GROUP BY keyword HAVING COUNT(id) > 10 ORDER BY keyword;

This might return many thousand rows similar to:
+---------------------------+--------+
| keyword                | clicks |
+---------------------------+--------+
| advertising pay per click |     11 |
| advertising statistics    |     29 |
| keyword management        |     13 |
| pay per click software    |     14 |
+---------------------------+--------+
The application then loops through the result and issues separate 
queries for each keyword that are similar to:

mysql> SELECT COUNT(id) AS clicks FROM clicksTable WHERE 
keyword='$keywordFromPreviousQuery' AND domain = 'mydomain.com' AND 
click_date BETWEEN '2004-12-16' AND '2004-12-31' GROUP BY keyword HAVING 

COUNT(id) > $countFromPreviosQuery ORDER BY keyword;
As you can imagine this makes the app very slow and nearly unusable. 
Unfortunately, my job is to make it usable again. :-)

I'm trying to figure out a way to do this in one query (or at least not 
have to send tons of queries over the network) but can't figure out how 
to aggregate the results.  I'd like to get a result similar to the 
following:

+---------------------------+--------+---------------+
| keyword                | previous clicks |  current clicks |
+---------------------------+--------+---------------+
| advertising pay per click |     11 |     15  |
| advertising statistics    |     29 |    30 |
| keyword management        |     13 |   29 |
| pay per click software    |     14 |    19  |
+---------------------------+--------+---------------+
I'm using MySQL 4.1 so perhaps some of the new features might help. ???
Thank you for the help!
--
Jim Grill


Jim, 

What you are doing is a form of crosstab or pivot table report based on 
date ranges. That means that if you want this to be a "dynamic" query you 
will have to build your statement in your code (it won't be static SQL). 
But to take your examples and convert them to a single query would look 
like this:

SELECT keyword,
        , SUM(if(click_date BETWEEN '2004-12-1' AND '2004-12-15',1,0)) as 
'previous clicks'
        , SUM(if(click_date BETWEEN '2004-12-16' AND '2004-12-31',1,0)) as 
'current clicks'
FROM clicksTable
WHERE click_date BETWEEN '2004-12-1' AND '2004-12-15'
        OR click_date BETWEEN '2004-12-16' AND '2004-12-31'
GROUP BY keyword
HAVING `previous clicks` > 10 
        OR `current clicks` > 10;

For different ranges (different sets of weeks) you will have to build your 
SQL statement to match. I know I could have used one large date span in my 
WHERE statement but that wouldn't have shown you where your column 
conditions and your WHERE conditions matched up.

Using this pattern you can compare this week to last week, this year to 
last year, or any other sets of ranges.  The HAVING statement filters the 
results so that you get only those keywords that were clicked on more than 
10 times during either range.

HTH,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Thank you very much, Shawn!  That pattern makes perfect sense to me and 
helps solve my problem.

Thank you for taking the time to show me that.
Regards,
Jim Grill
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]