SQL sentences of others-CodePudding

There is that SQL sentence which gets from xtu.dash() all id_units with de freq and lat and log (latitude and longitude)

SELECT 
    du.id_unit , du.freq_int, pos.lat, pos.lon
FROM 
    xtu.dash(now()::timestamp - interval '1 h', now()::timestamp)  du
INNER JOIN
    flt_lst_geo() pos ON pos.sdes = du.plate
WHERE  
    du.freq_int IS NOT NULL
    AND du.freq_int > 45 
GROUP BY 
    du.freq_int, pos.lat, pos.lon

What I need is to get an average longitude and latitude and also the total number of id_units.

SELECT 
    COUNT (tm.du.id_unit), AVG(tm.pos.lat), AVG (tm.pos.lon)
FROM 
    (SELECT 
         du.id_unit , du.freq_int, pos.lat, pos.lon
     FROM 
         xtu.dash($__timeFrom()::timestamp, $__timeTo()::timestamp) du
    INNER JOIN
        flt_lst_geo() pos ON pos.sdes = du.plate
    WHERE  
        du.freq_int IS NOT NULL
        AND du.freq_int > 45 
    GROUP BY 
        du.freq_int, pos.lat, pos.lon) AS tm

but this approach doesn't work

CodePudding user response：


with main as (
SELECT 
du.id_unit as id_unit, 
du.freq_int as freq_int, 
pos.lat as lat, 
pos.lon as lon
FROM xtu.dash($__timeFrom()::timestamp, $__timeTo()::timestamp) du
inner join flt_lst_geo() pos on pos.sdes=du.plate
and du.freq_int is not null and du.freq_int > 45 
)

select 
count(id_unit) as total_id, 
avg(freq) as avg_freq, 
avg(lat) as avg_lat
from main