How to correctly use AVG() in SQL?

3501 views sql
8

I track base station performance in an RF network by storing SSI values in this table:

TABLE BASE_SSI:
    wayside     BIGINT (PK)
    timestamp   datetime
    base        varchar(12)
    ssi         int

SSI is read once per hour and stored in this table with about one years' worth of readings.

I can extract one days' worth of SSI values for any given day in the past with this query:

select wayside,timestamp,base,ssi from BASE_SSI 
where wayside=225520220000 
and timestamp > DATEADD(day,-7,getdate())
and timestamp < DATEADD(day,-6,getdate())
order by timestamp desc

which gives me these results:

wayside         timestamp             base        ssi
225520220000    2018-11-02 00:21:09   423.3.01    33
225520220000    2018-11-01 22:31:03   423.3.01    32
225520220000    2018-11-01 20:40:53   423.3.01    32
225520220000    2018-11-01 18:50:45   423.3.01    31
225520220000    2018-11-01 17:00:35   423.3.01    33
225520220000    2018-11-01 15:10:26   423.3.01    34
225520220000    2018-11-01 13:20:20   423.3.01    38
225520220000    2018-11-01 11:30:11   423.3.01    37
225520220000    2018-11-01 09:40:03   423.3.01    35
225520220000    2018-11-01 07:49:03   423.3.01    35
225520220000    2018-11-01 05:59:50   423.3.01    34
225520220000    2018-11-01 04:09:43   423.3.01    34
225520220000    2018-11-01 02:19:34   423.3.01    34

What I need is a query that gives me the AVERAGE ssi for this 24-hour period in the past, for all waysides. The results should be:

wayside         date         base        avg_ssi
225520220000    2018-11-01   423.3.01    34
225520230000    2018-11-01   423.2.21    21
225520240000    2018-11-01   423.4.11    18
225520250000    2018-11-01   423.1.21    55
225520260000    2018-11-01   422.2.01    62
225520270000    2018-11-01   452.3.07    33
225520280000    2018-11-01   425.1.03    25

I only need integral values for average ssi, as shown.

I've tried using AVG(ssi) in a SELECT statement, but I am not sure how to apply this to all wayside values in a selected time period. For example, in the original query, I add 'AVG(ssi) as avg_ssi' in the SELECT line but it only yields the 'average' SSI for each single record, which is the same as the SSI value.

My pseudo-SQL version of this query would be 'display the 24-hour average SSI of every wayside for a given date'.

answered question

1 Answer

9

You can try to use avg with group by on CONVERT(char(10), timestamp,126) let datetime to yyyy-MM-dd format group by each date.

select wayside,group by CONVERT(char(10), timestamp,126),base,avg(ssi) as avg_ssi
from BASE_SSI 
where timestamp > DATEADD(day,-7,getdate()) and timestamp < DATEADD(day,-6,getdate())
group by CONVERT(char(10), timestamp,126),base,wayside
order by timestamp desc

posted this

Have an answer?

JD

Please login first before posting an answer.