aggregate count and group by

[Jim Grill]

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]