Menu
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.
519
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.
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).
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.
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.
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.
74
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
