CODEWITHSUNDEEP
sql
user3783811
user3783811

Reputation: 31

SQL compare averages against specific rows in a table

I have a single table as below what im trying to achieve is to output customers that have a higher weight than the average for that sex, so I would only compare them against the same sex, the only matching 2 below are c3 and c6 as they exceed this.

cust_id - weight - gender
c1      -  70       m
c2      -  70       m
c3      -  71       m
c4      -  60       f 
c5      -  60       f
c6      -  61       f

so I would expect the table to look like the below

  cust_id - weight - gender
    c3      -  71       m
    c6      -  61       f

The below is what I have thus far however this does not account for the same sex comparison, I think that is just comparing the average of the entire column, could anyone lend a hand where im going wrong? many thanks. 

select 
    cust_id,
    gender,
    weight
from customer c
GROUP BY c.cust_id, c.gender, c.weight
HAVING AVG(weight) > (SELECT AVG(weight)  FROM customer)
order by c.gender

Upvotes: 0

Views: 84

Answers (1)

Kiran Hegde
Kiran Hegde

Reputation: 3681

Try using this query

select 
    cust_id,
    gender,
    weight
from customer c
WHERE weight >  (SELECT AVG(weight)  
                 FROM customer 
                 WHERE gender = c.gender)
order by c.gender

Upvotes: 2

Related Questions