Is there a way to join multiple, single line rows into one table in SQL Server?

I have a dataset that logs a value MINUTELY. I need to get the average of that column every day in a specific month. So far, my frankenstein query works and returns the data I need. I verified that it’s correct and all. The dates are a day off because technically they’re yesterday’s readings. Anyways, why does this query return what seems to be 31 separate tables with 1 row each instead of 1 table with 31 rows? Is there a way to join this all into one table?

SET NOCOUNT ON;
DECLARE @StartDate DateTime
SET @StartDate = dateadd(day,datediff(day,1,getdate()),0) -- 11.12.2023

DECLARE @EndDate DateTime
SET @EndDate = dateadd(day,datediff(day,0,getdate()),0) --11.13.2023

DECLARE @CurrentMonthStart DateTime
SET @CurrentMonthStart = dateadd(month,datediff(month,0,getdate()),0) -- 11.01.2023 stop date for last month
       
DECLARE @LastMonthStart DateTime
SET @LastMonthStart = dateadd(month,-1,@CurrentMonthStart +1) -- 10.02.2023 start with first day of month

DECLARE @EndDateCycle DateTime
SET @EndDateCycle = dateadd(day,1, @LastMonthStart) -- 10.03.2023 reading available full day after

-- Inflow

SET @StartDate = @LastMonthStart -- 10.02.2023
SET @EndDate = @EndDateCycle -- 10.03.2023
WHILE @StartDate <= @CurrentMonthStart -- check to make sure we're still in last month
    BEGIN
        DECLARE @AP Float
        SET @AP = (SELECT
              Avg(AP_INF_FT01MGD_VAL0) --this gets one value, average of the minutely data 
        FROM MINUTELY
        WHERE timestamp >= @StartDate --to loop forward in month, day to day
        AND timestamp < @EndDate)
    SELECT 'Apollo' = @AP,
        'Reading Date' = @StartDate
    WHERE @AP IS NOT NULL -- had to add because I'd get an extra reading with NULL (investigate)
    SET @StartDate = @StartDate + 1 --plus 1 to loop through the month
    SET @EndDate    = @EndDate + 1
    END

I expected 31 rows of data that were numbered 1 to 31. But instead I have 31 single rows that all seem like their own table:

 | Apollo | Reading Date |
1| 22.243 | 2023-10-02 00|

 | Apollo | Reading Date |
1| 22.531 | 2023-10-03 00|

 | Apollo | Reading Date |
1| 21.267 | 2023-10-04 00| etc.. .

I expected to see something more like this:

 | Apollo | Reading Date |
1| 22.243 | 2023-10-02 00|
2| 22.531 | 2023-10-03 00|
3| 21.267 | 2023-10-04 00| etc.

Sorry about the formatting, I promise I’m trying my best to learn how to format and ask questions correctly. Usually I can find everything I need after a couple clicks of a well-worded Google search, but this one has me stumped. Thanks!

>Solution :

It would be helpful if you included sample data in your question so we can tailor an answer to your specific use case.

Regardless, the problem you have is you’re looping over the results. Although there are exceptions, if you find yourself using loops in SQL, you should really reconsider your approach, as you’re almost always better off doing things set based. In particular, in this case, you are, as you saw, projecting 31 individual data sets where what you really want is one data set. That’s your clue that you should only have 1 query.

Ditch the loop at do something like this:

select 
    TheDate = convert(date, [timestamp]),
    TheAvg = avg(AP_INF_FT01MGD_VAL0)
from minutely
where [Timestamp] >= @StartOfMonth and [Timestamp] < @EndOfMonth
group by convert(date, [timestamp])