Question: I'm trying to write a SQL Query that has to return how many transactions were made in the last 10 minutes for each minute. Like:

1 minute ago : 2 transactions

2 minutes ago : 1 transaction

...

9 minutes ago : 3 transactions

10 minutes ago : 4 transactions

I was trying to do this query:

```
 DECLARE @N int =  10;
 DECLARE @NOW DATETIME =  GETDATE();

 WITH numbers( num ) AS (
     SELECT 1 UNION ALL
     SELECT 1 + num FROM numbers WHERE num < @N )

 SELECT num AS minute,
 ( 
     SELECT COUNT(*) AS RESULTS
     FROM  [ApiTransactions] 
     WHERE [DateUtc] > DATEADD(year, -1, @NOW)
     GROUP BY DATEPART(minute, DATEADD(minute, -num, @NOW))

 )
 FROM numbers;

```

I still don't know if the logic is right. What I know is that I'm receiving the error:

> 
> Each GROUP BY expression must contain at least one column that is not an outer reference.
> 
> 
> 

Why I'm having this error? Is there a better way to do the query?

Answer: 
You don't need a numbers table for this, unless you need to fill in times with no transactions. I would start with this:

```
SELECT DATEADD(minute, DATEDIFF(minute, 0, DateUtc), 0) as the_minute, COUNT(*)
FROM ApiTransactions
WHERE DateUtc > DATEADD(minute, -10, DateUtc)
GROUP BY DATEADD(minute, DATEDIFF(minute, 0, DateUtc), 0)
ORDER BY the_minute;

```
You can use this one:

```
SELECT  DATEDIFF(minute,[DateUtc],GETDATE()) as minutes_ago,
        COUNT(*) tran_count 
FROM ApiTransactions
WHERE DATEDIFF(second,[DateUtc],GETDATE()) <= 600 -- 10 minutes ago
GROUP BY DATEDIFF(minute,[DateUtc],GETDATE())

```

Output:

```
minutes_ago tran_count
0           2
1           3
2           10
3           15
4           4
5           9
6           12
7           6
8           13
9           4
10          2

```