Ignore certain value with AVG

Question

Ignore certain value with AVG

Navigation

#1 by (0 votes)

0

I would like the calculation AVG to ignore the values above 100000 (10 seconds), with the following query:

user_id,
COUNT(*) as total,
AVG('exec_time') as tempoMedio,
SUM(CASE WHEN 'success' = 1 THEN 1 ELSE 0 END) inTime,
SUM(CASE WHEN 'exec_time' <= 0.900 THEN 1 ELSE 0 END) above900,
SUM(CASE WHEN 'exec_time' <= 3000 THEN 1 ELSE 0 END) above3000
FROM MYTABLE WHERE 'created_at' BETWEEN "DATA 1" AND "DATA 2"

The main idea is:

On a certain date pick up; the user ID, the average time of the column exec_time ignoring values above 10000 , the number of records that have the success column as 1, the amount of exec_time that are <= 0.900 and the amount of% exec_time <= 3000

mysql sql

asked by anonymous 25.10.2017 / 19:31

1 answer

Does Mysqli only execute if it has a submit? Unknown provider: PermPermissionStoreProvider

score 0 · Answer 1

Viewing your select I believe that a group by ...

To make the filter you can use the clause having

Select
    user_id,
    COUNT(*) as total,
    AVG('exec_time') as tempoMedio,
    SUM(CASE WHEN 'success' = 1 THEN 1 ELSE 0 END) inTime,
    SUM(CASE WHEN 'exec_time' <= 0.900 THEN 1 ELSE 0 END) above900,
    SUM(CASE WHEN 'exec_time' <= 3000 THEN 1 ELSE 0 END) above3000
FROM MYTABLE 
WHERE 'created_at' BETWEEN "DATA 1" AND "DATA 2"

-- vaja a falta do group by
group by user_id, success, exec_time

-- se for o exec_time
having exec_time <= 10000

-- Ou se for a média
having tempoMedio <= 10000