How to Group by Day in sql server?
Grouping By Day Of month:
In order to group the data by day you can use sql server in built DAY() funtion.
Grouping By Day Of week:
For this you can use DATEPART(weekday,[date column])
Grouping By week:
For this you can use DATEPART(week,[date column])
Grouping By DAY of Year:
For this you can use DATEPART(dayofyear,[date column])
Let us suppose the column which contains date/datetime values is solddatetime.
To group by day of month you can use:
[sql]
GROUP BY DAY(SalesDateTime)
[/sql]
To group by Day of week you can use:
[sql]
GROUP BY DATEPART(weekday,SalesDateTime)
[/sql]
To group by week you can use:
[sql]
GROUP BY DATEPART(week,SalesDateTime)
[/sql]
To group by Day of year you can use:
[sql]
GROUP BY DATEPART(dayofyear,SalesDateTime)
[/sql]
Complete example:
[sql]
CREATE TABLE #Sales
(
Name VARCHAR (100),
SalesDateTime DATETIME
)
GO
INSERT INTO #Sales
SELECT ‘Product1’,
‘2012-04-01 00:00:00.000’
UNION ALL
SELECT ‘Product2’,
‘2012-04-02 00:00:00.000’
UNION ALL
SELECT ‘Product3’,
‘2012-04-02 00:00:00.000’
UNION ALL
SELECT ‘Product4’,
‘2012-04-03 00:00:00.000’
UNION ALL
SELECT ‘Product5’,
‘2012-04-03 00:00:00.000’
UNION ALL
SELECT ‘Product1’,
‘2012-04-30 00:00:00.000’
UNION ALL
SELECT ‘Product1’,
‘2012-04-30 00:00:00.000’
UNION ALL
SELECT ‘Product1’,
‘2012-05-02 00:00:00.000’
UNION ALL
SELECT ‘Product5’,
‘2012-05-02 00:00:00.000’
UNION ALL
SELECT ‘Product5’,
‘2012-05-02 00:00:00.000’
GO
–GROUP BY DAY of Month
SELECT count(*) AS SalesCount, DAY(SalesDateTime) Day
FROM #Sales
GROUP BY DAY(SalesDateTime)
–GROUP BY DAY of Week
SELECT count(*) AS SalesCount, DATEPART(weekday,SalesDateTime) Day
FROM #Sales
GROUP BY DATEPART(weekday,SalesDateTime)
–GROUP BY Week
SELECT count(*) AS SalesCount, DATEPART(week,SalesDateTime) Day
FROM #Sales
GROUP BY DATEPART(week,SalesDateTime)
–GROUP BY DAY of Year
SELECT count(*) AS SalesCount, DATEPART(dayofyear,SalesDateTime) Day
FROM #Sales
GROUP BY DATEPART(dayofyear,SalesDateTime)
GO
DROP TABLE #Sales
[/sql]