How can I group by time in SQL

Question

Possible Duplicate:
SQL Query Group By Datetime problem?

I am working on an application with 2 steps.

Scan logs and persist data from them in a database.
Read data from database and visualize the data.

The first step is more or less finished. I try to explain the background and my reguirement with the second step.

Each row in the database consists of some info like logdate, logfilename, LogType, logMessage etc. So I want for example write SQL that summarize a given LogType per day.

This is the columns:

[LogDate] [datetime] NOT NULL,
[Computer] [varchar](50) NOT NULL,
[Type] [varchar](50) NOT NULL,
[FileName] [varchar](100) NOT NULL,
[LineNo] [int] NOT NULL,
[UserName] [varchar](50) NOT NULL,
[Message] [varchar](max) NOT NULL,

I imagine the output could be like this if I want to show all rows with Type=TDBError:

Date        Sum
2012-10-01  3
2012-10-02  12
2012-10-03  40
2012-10-05  24
2012-10-06  18

So at date 2012-10-01 there was 3 rows in DB where Type=TDBError. At date 2012-10-02 there was 12 etc.

How should I write the SQL for this ?

So you don't want a row for 10-04 with a sum of 0? Also, please specify the version of SQL Server. — Aaron Bertrand, Jan 16 '13 at 20:28

score 1 · Answer 1 · answered Jan 16 '13 at 20:28

1

GROUP BY DATEPART(day, date), DATEPART(month, date), DATEPART(year, date)

answered Jan 16 '13 at 20:28

flup

26,937
7
52
74

score 1 · Accepted Answer · answered Jan 16 '13 at 20:29

1

Assuming SQL Server 2008 or newer:

SELECT 
  [Date] = CONVERT(DATE, LogDate), 
  [Sum] = COUNT(*)
FROM dbo.Log_Table_Name
WHERE [Type] = 'DBError'
GROUP BY CONVERT(DATE, LogDate)
ORDER BY [Date];

answered Jan 16 '13 at 20:29

Aaron Bertrand

272,866
37
466
490

score 1 · Answer 3 · answered Jan 16 '13 at 20:30

1

You can do a group by the parts of the time

GROUP BY date(log_date), month(log_date), day(log_date)

answered Jan 16 '13 at 20:30

Marlin Pierce

9,931
4
30
52

bummi · Answer 4 · 2013-01-16T21:03:59.227

1

Select Cast(FLOOR(CAST(DATE as float)) as DateTime) as Date,COUNT(*) as [SUM]
from Log_Table_Name
Group by Cast(FLOOR(CAST(DATE as float)) as DateTime)
order by Cast(FLOOR(CAST(DATE as float)) as DateTime)

edited Jan 16 '13 at 21:03

answered Jan 16 '13 at 20:33

bummi

27,123
14
62
101

Just FYI, all those converts are not free, and I've proven that (albeit in some cases only slightly) a native convert to date is the fastest approach : http://www.sqlperformance.com/2012/09/t-sql-queries/what-is-the-most-efficient-way-to-trim-time-from-datetime & http://www.sqlperformance.com/2012/10/t-sql-queries/trim-time – Aaron Bertrand Jan 16 '13 at 20:36

How can I group by time in SQL

4 Answers4

Linked