Ignore a certain value with AVG

Asked

Viewed 40 times

0

I wish I could calculate it AVG 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; user id, column average time exec_time ignoring values above 10000, the amount of records that hold the column success as 1, the amount of exec_time that are <= 0.900 and the amount of exec_time <= 3000

mysql sql

  • you speak the value before AVG exec_time or the result of it ?

  • @Marconciliosouza the result of it, in the average calculation I would not like to include values above 10000.

1 answer

0

Seeing your select I believe a group by is missing ...

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

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.