Posted by Erland Sommarskog on 03/24/06 01:15

jerball (jerball@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]