How can I group rows in Table by day?-CodePudding

I have a SQL Table with columns of

Timestamp	Region
2022-10-06 01:00:00.0000000	East
2022-10-06 03:00:00.0000000	East
2022-10-06 05:00:00.0000000	West
2022-10-06 01:00:00.0000000	East
2022-10-07 05:00:00.0000000	West
2022-10-08 05:00:00.0000000	East
2022-10-09 01:00:00.0000000	West
2022-10-09 01:00:00.0000000	East

So I want to group the table by 'Day' and 'Region'

Timestamp	Region	Count
2022-10-06	East	3
2022-10-06	West	1
2022-10-07	West	1
2022-10-08	East	1
2022-10-09	West	1
2022-10-09	East	1

So I try this with SQL

SELECT 
[region], 
CONCAT( datepart(YEAR, [Timestamp]), '-',  datepart(MONTH, [Timestamp]), '-', datepart(DAY, [Timestamp])) AS dayEvent,
FROM Table
GROUP BY [region], [dayEvent]

But I get error saying 'Timestamp is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

Can u please tell me how can I fix this?

CodePudding user response：

We can cast(Timestamp as date) and that would give us the results we want.

select   cast(Timestamp as date) as Timestamp
        ,Region
        ,count(*)                as count
from     t
group by cast(Timestamp as date), Region

Timestamp	Region	count
2022-10-06	East	3
2022-10-08	East	1
2022-10-09	East	1
2022-10-06	West	1
2022-10-07	West	1
2022-10-09	West	1

Fiddle

CodePudding user response：

In SQL Server you can simplify by the use of cross apply which allows you to reference it in the select and group by, and the convert function to extract the required date format:

select ts [Timestamp], Region, Count(*) [count]
from t
cross apply(values(Convert(char(10),[Timestamp], 23)))x(ts)
group by ts, Region;

CodePudding user response：

SELECT 
  [region], 
  CONCAT( datepart(YEAR, [Timestamp]), '-',  datepart(MONTH, [Timestamp]), '-', datepart(DAY, [Timestamp])) AS dayEvent,
  COUNT(*) AS Count
FROM Table
GROUP BY datepart(YEAR, [Timestamp]), datepart(MONTH, [Timestamp]), datepart(DAY, [Timestamp])

CodePudding user response：

You may group by CAST([Timestamp] AS DATE), Region as the following:

SELECT CAST([Timestamp] AS DATE) Timestamp, Region, COUNT(*) [Count]
FROM table_name
GROUP BY CAST([Timestamp] AS DATE), Region
ORDER BY Timestamp, Region

See a demo.