Average on table joined with right join-CodePudding

I want to average on tables I joined with right join, but I get group by error, I tried grouping by column I got the error but it still didn't work

Select 
t1.[TimeCol],
t1.[table1sampleColumn] AS t1sC,
AVG(t1.[table1sampleColumn]) as avgt1sC,
t2.[table2sampleColumn] AS t2sC,
AVG(t2.[table2sampleColumn]) as avgt2sC,
t2.[table2sampleColumn2] as t2sC2,
AVG(t2.[table2sampleColumn2]) as avgt2sC2


FROM dbo.table1 t1
INNER JOIN dbo.table2 t2
ON CAST(t1.TimeCol AS SMALLDATETIME) = CAST(t2.LocalCol AS SMALLDATETIME)

WHERE t1.TimeCol BETWEEN '2022-01-27 07:00:00' and '2022-01-27 07:05:00'
ORDER BY TimeCol DESC

The query works when there are no AVG, but I want to AVG. how can I do that

error:

Column 'dbo.table1.TimeCol' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

my result without averaging

TimeCol                    t1sC     t2sC t2sC2
2022-01-27 07:04:13.850    271      164   299
2022-01-27 07:03:13.850    271      167   299
2022-01-27 07:02:13.847    269      170   298
2022-01-27 07:01:13.840    269      173   297
2022-01-27 07:00:13.830    269      176   296

the output i want with average

TimeCol                        t1sC    avgt1sC  t2sC   avgt2sC  t2sC2  avgt2sC2
2022-01-27 07:04:13.850        271      270     164    170      299    298
2022-01-27 07:03:13.850        271              167             299    
2022-01-27 07:02:13.847        269              170             298    
2022-01-27 07:01:13.840        269              173             297    
2022-01-27 07:00:13.830        269              176             296

CodePudding user response：

Seems you want a windowed aggregate here, which would look like this:

Select  t1.[TimeCol],
        t1.[table1sampleColumn] AS t1sC,
        AVG(t1.[table1sampleColumn]) OVER () as avgt1sC,
        t2.[table2sampleColumn] AS t2sC,
        AVG(t2.[table2sampleColumn]) OVER () as avgt2sC,
        t2.[table2sampleColumn2] as t2sC2,
        AVG(t2.[table2sampleColumn2]) OVER () as avgt2sC2
FROM dbo.table1 t1
     INNER JOIN dbo.table2 t2 ON CAST(t1.TimeCol AS SMALLDATETIME) = CAST(t2.LocalCol AS SMALLDATETIME) --This doesn't look right, and likely need improving
WHERE t1.TimeCol >= '2022-01-27T07:00:00'
  AND t1.TimeCol <= '2022-01-27 07:05:00' --I assume you do want <= here, as you used BETWEEN, but you probably actually want <
ORDER BY TimeCol DESC;

CodePudding user response：

It is not working thus you did not use group by clause. You should use it and include all other columns(t1.[TimeCol],t1.[table1sampleColumn],t2.[table2sampleColumn],t2.[table2sampleColumn2]) to the group by clause.