|
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!
Navigation:
[Reply to this message]
|