Posted by jerball on 03/24/06 00:43

A client wants to keep track of the number of searches for keywords in
a date range. So, I'm storing each occurance of a search in a table.

The columns are:

PK: id
search_string
search_date

I'm trying to wrap my head around how I would select the number of
occurances for each string, divided by days. The desired result would
look something like:

search_date search_string numOccurances
----------------------------------------------------------
March 20 dogs 4
March 20 pigs 2
March 21 dogs 8
March 22 pigs 3
March 22 pigeons 5

I've tried a query like:


select search_string, CONVERT(CHAR(11),search_date,106) as search_date,
count(search_string) as numOccurances
from searches
where search_date >= dateadd(d,-3,getdate())
group by search_date, search_string


but it doesn't give me the desired results. I'm sure I'm just looking
at it the wrong way.

Suggestions?

Thanks!

[Back to original message]