Group by Day – With Examples

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]

Leave a Reply

Your email address will not be published. Required fields are marked *