news:1155829653.947381.217090@75g2000cwc.googlegroups.com...
> Hello,
>
> I am looking to group my query by month and having some problems.
>
> My Query:
>
> SELECT     CONVERT(varchar, DateStamp, 110) AS DateAdded,
> COUNT(CONVERT(varchar, DateStamp, 110)) AS [#_of_Articles]
> FROM         InstantKB_Articles
> GROUP BY CONVERT(varchar, DateStamp, 110),
> ORDER BY dateadded
>
> This result is:
>
> 01/02/2006     5
> 01/05/2006     9
> 03/12/2006     10
>
> I am looking to change the query so that this will be grouped my month
> What I am looking for is:
>
> Jan-06     14
> Mar-06    10
>
> Thank you in advance
>
> -L
>

> SELECT Year(DateStamp) As [Year],
>    Month(DateStamp) As [Month],
>    COUNT(*) AS [#_of_Articles]
> FROM         InstantKB_Articles
> GROUP BY Year(DateStamp), Month(DateStamp)
> ORDER BY Year(DateStamp), Month(DateStamp)
>
> will group the counts by month and year.
>
> Tom
>
> <mur***@gmail.com> wrote in message
> news:1155829653.947381.217090@75g2000cwc.googlegroups.com...
> > Hello,
> >
> > I am looking to group my query by month and having some problems.
> >
> > My Query:
> >
> > SELECT     CONVERT(varchar, DateStamp, 110) AS DateAdded,
> > COUNT(CONVERT(varchar, DateStamp, 110)) AS [#_of_Articles]
> > FROM         InstantKB_Articles
> > GROUP BY CONVERT(varchar, DateStamp, 110),
> > ORDER BY dateadded
> >
> > This result is:
> >
> > 01/02/2006     5
> > 01/05/2006     9
> > 03/12/2006     10
> >
> > I am looking to change the query so that this will be grouped my month
> > What I am looking for is:
> >
> > Jan-06     14
> > Mar-06    10
> >
> > Thank you in advance
> >
> > -L
> >

news:1155832790.227000.244020@h48g2000cwc.googlegroups.com...
> Tom,
>
> Thank you for your prompt reply I appreciate it.
>
> To get it into Jan-2006 format should I put checks for 2=Feb ?
>
> What would be the best way of doing it?
>
> Thanks again in advance
>
>
> Tom Cooper wrote:
>> SELECT Year(DateStamp) As [Year],
>>    Month(DateStamp) As [Month],
>>    COUNT(*) AS [#_of_Articles]
>> FROM         InstantKB_Articles
>> GROUP BY Year(DateStamp), Month(DateStamp)
>> ORDER BY Year(DateStamp), Month(DateStamp)
>>
>> will group the counts by month and year.
>>
>> Tom
>>
>> <mur***@gmail.com> wrote in message
>> news:1155829653.947381.217090@75g2000cwc.googlegroups.com...
>> > Hello,
>> >
>> > I am looking to group my query by month and having some problems.
>> >
>> > My Query:
>> >
>> > SELECT     CONVERT(varchar, DateStamp, 110) AS DateAdded,
>> > COUNT(CONVERT(varchar, DateStamp, 110)) AS [#_of_Articles]
>> > FROM         InstantKB_Articles
>> > GROUP BY CONVERT(varchar, DateStamp, 110),
>> > ORDER BY dateadded
>> >
>> > This result is:
>> >
>> > 01/02/2006     5
>> > 01/05/2006     9
>> > 03/12/2006     10
>> >
>> > I am looking to change the query so that this will be grouped my month
>> > What I am looking for is:
>> >
>> > Jan-06     14
>> > Mar-06    10
>> >
>> > Thank you in advance
>> >
>> > -L
>> >
>

> I like this approach better than having 12 case statements and conditional
> formatting (however it will depend on regional/language settings).  I used
> the subqueries to eliminate needing to reproduce calculations.  If you only
> want to include specific date ranges (e.g. this year only), put the where
> clause on the innermost query, where you can take advantage of an index on
> DateStamp (if one exists, and it probably should).
>
>
>
> SELECT
>     [Month] = CONVERT(CHAR(3), dt, 7) + '-' + RTRIM(YEAR(dt)),
>     [#_Of_Articles] = c
> FROM
> (
>     SELECT
>         dt,
>         c = COUNT(*) FROM
>         (
>             SELECT
>             dt = DATEADD(DAY, 1, DATEDIFF(DAY, DAY(DateStamp), DateStamp))
>             FROM  InstantKB_Articles
>         ) x1
>     GROUP BY dt
> ) x2
> ORDER BY dt
>
>
>
>
>
> <mur***@gmail.com> wrote in message
> news:1155832790.227000.244020@h48g2000cwc.googlegroups.com...
> > Tom,
> >
> > Thank you for your prompt reply I appreciate it.
> >
> > To get it into Jan-2006 format should I put checks for 2=Feb ?
> >
> > What would be the best way of doing it?
> >
> > Thanks again in advance
> >
> >
> > Tom Cooper wrote:
> >> SELECT Year(DateStamp) As [Year],
> >>    Month(DateStamp) As [Month],
> >>    COUNT(*) AS [#_of_Articles]
> >> FROM         InstantKB_Articles
> >> GROUP BY Year(DateStamp), Month(DateStamp)
> >> ORDER BY Year(DateStamp), Month(DateStamp)
> >>
> >> will group the counts by month and year.
> >>
> >> Tom
> >>
> >> <mur***@gmail.com> wrote in message
> >> news:1155829653.947381.217090@75g2000cwc.googlegroups.com...
> >> > Hello,
> >> >
> >> > I am looking to group my query by month and having some problems.
> >> >
> >> > My Query:
> >> >
> >> > SELECT     CONVERT(varchar, DateStamp, 110) AS DateAdded,
> >> > COUNT(CONVERT(varchar, DateStamp, 110)) AS [#_of_Articles]
> >> > FROM         InstantKB_Articles
> >> > GROUP BY CONVERT(varchar, DateStamp, 110),
> >> > ORDER BY dateadded
> >> >
> >> > This result is:
> >> >
> >> > 01/02/2006     5
> >> > 01/05/2006     9
> >> > 03/12/2006     10
> >> >
> >> > I am looking to change the query so that this will be grouped my month
> >> > What I am looking for is:
> >> >
> >> > Jan-06     14
> >> > Mar-06    10
> >> >
> >> > Thank you in advance
> >> >
> >> > -L
> >> >
> >