SQL Server - Group Records Like Archives By Month

I have a table Fixture with three fields.

ID | Fixture | Date
-------------------------
1  | 123456  |  20110515
2  | 123446  |  20110512
3  | 123476  |  20110411
4  | 123486  |  20110310

...and so on.

I need to group the records by date and want to get fixture counts.

Results would display like the following example. How can I achieve the results from SQL Query?

Archives

February 2011 (3)
January 2011 (6)
December 开发者_C百科2010 (10)
November 2010 (7)
October 2010 (5)

I need community help to solve this issue, kindly help me out.

---

How about something like

DECLARE @Table TABLE(
        ID INT,
        Fixture INT,
        Date DATETIME
)
INSERT INTO @Table SELECT 1,123456,'20110515' 
INSERT INTO @Table SELECT 2,123446,'20110512' 
INSERT INTO @Table SELECT 3,123476,'20110411' 
INSERT INTO @Table SELECT 4,123486,'20110310'

;WITH Vals AS (
        SELECT  DATENAME(month,Date) + ' ' + CAST(DATEPART(year,Date) AS VARCHAR(4)) DateValue,
                ID,
                CONVERT(VARCHAR(6), Date, 112) OrderValue 
        FROM    @Table
)
SELECT  DateValue,
        COUNT(ID) Cnt
FROM    Vals
GROUP BY    DateValue,
            OrderValue
ORDER BY    OrderValue

---

Try it, it has month numbers, you can update it in your code or in sql

 select COUNT(id), DATEPART(year, dtCreated) as y, DATEPART(MONTH,dtCreated) as mo 
    from TaxesSteps group by DATEPART(year, dtCreated), DATEPART(MONTH,dtCreated)
    order by 2 desc, 3 desc

---

I believe this code will do what you require:

SELECT
    DATENAME(m, [Date]) + ' ' + CAST(YEAR([Date]) AS VARCHAR(4)) AS ArchiveMonth
    ,COUNT(ID) AS Items
FROM
    Fixture
GROUP BY
    DATENAME(m, [Date]) + ' ' + CAST(YEAR([Date]) AS VARCHAR(4))

(dang it... already beaten)

---

Declare @table table (ID bigint identity(1,1), Fixture nvarchar(100), [Date] nvarchar(100))

INSERT INTO @table values ('123456','20110515')
INSERT INTO @table values ('123256','20110410')
INSERT INTO @table values ('123356','20110511')
INSERT INTO @table values ('122456','20110503')

--select DATEPART(month,0, (cast([date],datetime) from @table
SELECT DATENAME(month, CAST([Date] as datetime))+ ' ' + DATENAME(Year,CAST([Date] as datetime)) + ' (' + CAST(COUNT(Fixture) as varchar(100)) + ') '
from @table
group by DATENAME(month, CAST([Date] as datetime))+ ' ' + DATENAME(Year,CAST([Date] as datetime))

---

I give you three options, with 3 outputs as displayed

Option #1

select   convert(char(6), Date, 112) MonthYear, count(*) CountFixtures
from     Fixture
group by convert(char(6), Date, 112)
order by convert(char(6), Date, 112)

Output #1 - the most basic. The front end can work out the month and year names:

MonthYear CountFixtures
--------- -------------
201103    1
201104    1
201105    2

---

Option #2

select   datename(month, convert(datetime,convert(char(6), Date, 112)+'01'))
         + ' '
         + left(convert(char(6), Date, 112),4) MonthYear,
         count(*) CountFixtures
from     Fixture
group by convert(char(6), Date, 112)
order by convert(char(6), Date, 112)

Output #2 - recommended. Count and dates are separate fields

MonthYear                           CountFixtures
----------------------------------- -------------
March 2011                          1
April 2011                          1
May 2011                            2

---

Option #3

select   datename(month, convert(datetime,convert(char(6), Date, 112)+'01'))
         + ' '
         + left(convert(char(6), Date, 112),4)
         + ' ('
         + convert(varchar,count(*))
         + ')' FixturesByMonth
from     Fixture
group by convert(char(6), Date, 112)
order by convert(char(6), Date, 112)

Output #3 - exactly as you have in the question, with brackets. However, I strongly believe formatting (brackets et al) is a front end task, not SQL Server side.

FixturesByMonth
----------------
March 2011 (1)
April 2011 (1)
May 2011 (2)