1

I have a sql query as follows:

SELECT COUNT(*)
FROM event_db.event_log el, event_db.event_type et
WHERE et.event_type_id = el.event_type_id AND et.name in ('kpi_stats_dumped') AND el.timestamp > str_to_date('2016,12,12','%Y,%m,%d') AND el.timestamp < str_to_date('2017,12,12','%Y,%m,%d')
GROUP BY et.name, date_format(el.timestamp, '%d-%m-%Y')
ORDER BY et.name, el.timestamp

Which will return the result as follows:

1440
1441
1546
1234
1235
33
224

I need to find the average of the values listed.(1021.85714286)

Can someone be able to help me with the same.

sathis
  • 115
  • 6

2 Answers2

1
select avg(cnt) as average_count
from 
(
    SELECT COUNT(*) as cnt
    FROM event_db.event_log el
    JOIN event_db.event_type et ON et.event_type_id = el.event_type_id
    WHERE et.name in ('kpi_stats_dumped') 
    AND el.timestamp > str_to_date('2016,12,12','%Y,%m,%d') 
    AND el.timestamp < str_to_date('2017,12,12','%Y,%m,%d')
    GROUP BY et.name, date_format(el.timestamp, '%d-%m-%Y')
) tmp
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • 1
    Just a hint - ordering in a subquery (which is needed here only for getting and set of values) is unnecessary and possibly will cause performance issues. – Kleskowy Jan 12 '17 at 10:18
0

I got the answer , Now the query is as follows:

SELECT avg(cnt) from 
(SELECT count(*) as cnt
FROM  event_db.event_log el ,
      event_db.event_type et
WHERE et.event_type_id = el.event_type_id
      AND et.name IN ( 'kpi_stats_dumped' )
      AND el.timestamp > str_to_date('2016,12,12', '%Y,%m,%d')
      AND el.timestamp < str_to_date('2017,12,12', '%Y,%m,%d')
GROUP BY et.name , date_format(el.timestamp, '%d-%m-%Y')
ORDER BY et.name, el.timestamp) tmp
sathis
  • 115
  • 6