Average and filter by values above this mean

Asked

Viewed 303 times

0

I would like to average the amounts spent by all customers. Then list the names of customers who spent above average (can be using inner join, but with sub-consultation):

SELECT a.primeiro_nome, AVG(b.valor) AS media
    FROM cliente AS a
    INNER JOIN pagamento AS b ON (a.cliente_id = b.cliente_id)
    GROUP BY a.cliente_id
      HAVING media > (SELECT 
        AVG(valor)
         FROM pagamento);
mysql sql

  • Welcome Felipe, try to bring as much detail into your question by showing how far your current code and table structure are.

  • Felipe Good night! if you can drill in better with codes becomes easier to help you.

  • Your query presented some problem?

  • I needed something more specific

1 answer

0

Calculate the overall average (average of averages) and then compare that of customers 

  SELECT a.primeiro_nome
        FROM cliente AS a
        INNER JOIN pagamento AS b ON (a.cliente_id = b.cliente_id)
        GROUP BY a.cliente_id
        having avg(b.valor) > (select avg(media) 
                               from
                               (
                                SELECT a.primeiro_nome, AVG(AVG(b.valor) 
                                INNER JOIN pagamento AS b ON (a.cliente_id = b.cliente_id)
                                GROUP BY a.cliente_id
                                ) media_geral)

Browser other questions tagged mysql sql

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