Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

TS SQL - group by minute

Tags:

sql-server

I have a table with timestamps. What is the proper query to get the records counts for each minute for the last hour.

I.e. if now is 2:25, I want to know how many record were between 1:25 and 1:26, 1:26 and 1:27, and so on, so I have 60 results.

like image 519
Sunny Milenov Avatar asked Apr 16 '09 22:04

Sunny Milenov


People also ask

How to do SQL GROUP BY?

Syntax: SELECT column1, function_name(column2) FROM table_name WHERE condition GROUP BY column1, column2 HAVING condition ORDER BY column1, column2; function_name: Name of the function used for example, SUM() , AVG(). table_name: Name of the table. condition: Condition used.

What is GROUP BY clause in SQL with example?

In SQL, the GROUP BY clause is used to group rows by one or more columns. For example, SELECT country, COUNT(*) AS number FROM Customers GROUP BY country; Run Code. Here, the SQL command groups the rows by the country column, and counts the number of each country (because of the COUNT() function).

Can you GROUP BY multiple columns in SQL?

We can use the group by multiple column technique to group multiple records into a single record. All the records that have the same values for the respective columns mentioned in the grouping criteria can be grouped as a single column using the group by multiple column technique.

When we use GROUP BY in SQL?

The SQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.


2 Answers

This will return a count of results for each minute (where you have records) in the last hour

SELECT DATEPART(n, time_stamp) AS minute, COUNT(*) as results
FROM table_name 
WHERE time_stamp > DATEADD(hh, -1, GETDATE())
GROUP BY DATEPART(n, time_stamp)

This may return less than 60 results, depending on the data. If you have to have 60 results, the query is slightly different. This uses a Common Table Expression to generate a list of 60 numbers and a correlated sub-query to get the results for each minute:

WITH numbers ( num ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + num FROM numbers WHERE num < 60 )
SELECT num AS minute,
    (SELECT COUNT(*) AS results
    FROM table_name
    WHERE DATEPART(n, time_stamp) = num
    AND time_stamp > DATEADD(hh, -1, GETDATE())
FROM numbers

To see the results, replace DATEADD(hh, -1, GETDATE()) with DATEADD(mi, -15, GETDATE()) and you'll get the results for the last 15 minutes and 0 for other minutes.

like image 74
Robin Minto Avatar answered Oct 17 '22 13:10

Robin Minto


This is an alternative I have found useful for determining how many records are inserted or updated per minute. The nice thing about having your date format as a variable up front is that you can easily change it to analyze per hour instead. Hope this helps!

DECLARE @dateFormat as varchar(max) = 'yyyy-MM-dd HH:mm'

SELECT format(timeColumn, @dateFormat) AS minute, COUNT(*) as results
FROM yourTable
WHERE timeColumn > DATEADD(hh, -1, GETDATE())
GROUP BY  format(timeColumn, @dateFormat)
ORDER BY 1
like image 30
Aeren Robinson Avatar answered Oct 17 '22 13:10

Aeren Robinson