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)
精彩评论