Group by Day – With Examples

Posted on : 02-05-2012 | By : Devi Prasad | In : SQL Server Basics, T-SQL Code Examples

0



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:

GROUP BY DAY(SalesDateTime)

To group by Day of week you can use:

GROUP BY DATEPART(weekday,SalesDateTime)

To group by week you can use:

GROUP BY DATEPART(week,SalesDateTime)

To group by Day of year you can use:

GROUP BY DATEPART(dayofyear,SalesDateTime)

Complete example:

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



Reference : Devi Prasad (sqlserverlearner.com)

Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.



Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.




Your Ad Here


Tags: , , , , , , , , ,

Read Full Article