Group by Day – Örnekleri ile

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])

Örnek:


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

 

Kaynak