GROUP BY Query

Hi all! Please help me! Suppose I have this in table
CallDate
9/17/2005
9/16/2005
8/20/2005
8/14/2005
8/11/2005
7/23/2005
and I want to query the table to have this result:
Sep 2005 2
Aug 2005 3
Jul 2005 1
Thanks!
SELECT SUBSTRING(CONVERT(VARCHAR,MIN(calldate),113),4,8) AS mth,
COUNT(*) AS cnt
FROM YourTable
GROUP BY DATEDIFF(MONTH,'20000101',calldate) ;
David Portas
SQL Server MVP
|||Hi
Maybe:
SELECT MONTH(calldate),YEAR(calldate),COUNT(*) as CNT
FROM MyTable
GROUP BY MONTH(calldate),YEAR(calldate)
OR
SELECT RIGHT(CONVERT(CHAR(11),calldate,113),8) as CallMonthYear,COUNT(*) as
CNT
FROM MyTable
GROUP BY RIGHT(CONVERT(CHAR(11),calldate,113),8)
John
"Stanley" <xstanley@.gmail.com> wrote in message
news:1126953269.031997.92620@.g14g2000cwa.googlegro ups.com...
> Hi all! Please help me! Suppose I have this in table
> CallDate
> 9/17/2005
> 9/16/2005
> 8/20/2005
> 8/14/2005
> 8/11/2005
> 7/23/2005
> and I want to query the table to have this result:
> Sep 2005 2
> Aug 2005 3
> Jul 2005 1
> Thanks!
>
|||SELECT COUNT(*),DATENAME(MONTH,CALLDATE)+' ' +
LTRIM(STR(DATENAME(YEAR,CALLDATE)))
FROM MYTABLE
GROUP BY DATENAME(MONTH,CALLDATE)+' ' +
LTRIM(STR(DATENAME(YEAR,CALLDATE)))
"Stanley" <xstanley@.gmail.com> wrote in message
news:1126953269.031997.92620@.g14g2000cwa.googlegro ups.com...
> Hi all! Please help me! Suppose I have this in table
> CallDate
> 9/17/2005
> 9/16/2005
> 8/20/2005
> 8/14/2005
> 8/11/2005
> 7/23/2005
> and I want to query the table to have this result:
> Sep 2005 2
> Aug 2005 3
> Jul 2005 1
> Thanks!
>
|||Thanks for all replies! You all are helpful people! Thanks