help with group by statement

[jerball]

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!

[DickChristoph]

Hi Jerball

Your only real problem is that "dateadd(d,-3,getdate())" run right now
3/23/3006 at 5:05pm returns
3/20/3006 at 5:05pm so those dated "3/20/2006" are excluded.

"cast(convert(varchar(8), getdate() -3, 112) as datetime)" will return
"3/20/2006" if run today.

I also added a order by to your query.

So the query is:

select search_string, CONVERT(CHAR(11),search_date,106) as search_date,
count(search_string) as numOccurances
from searches
where search_date >= cast(convert(varchar(8), getdate() -3, 112) as
datetime)
group by search_date, search_string
order by search_date, search_string

and I get
search_string search_date numOccurances
------------------- ----------- -------------
dogs 20 Mar 2006 4
pigs 20 Mar 2006 2
dogs 21 Mar 2006 8
pigeons 22 Mar 2006 5
pigs 22 Mar 2006 3

Which I believe is what you want.
--
-Dick Christoph
"jerball" <je*****@gmail.com> wrote in message
news:11**********************@u72g2000cwu.googlegr oups.com...

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!

[Erland Sommarskog]

jerball (je*****@gmail.com) writes:

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.

I don't know what the search_date in the GROUP BY clause is intended
to mean, but it refers to the table column, not your convert expression.
Either repear the convert in the GROUP BY, or use a derived table:

SELECT search_string, search_date, count(*)
FROM (SELECT search_string,
CONVERT(CHAR(11),search_date,106) as search_date
FROM searches
WHERE search_date >= dateadd(d, -3, getdate())) AS a
GROUP BY search_date

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx