CODEWITHSUNDEEP
mysqlsqlselect
Rav
Rav

Reputation: 1387

Average a certain column value in MySQL

I have this table structure:

id | code | rating 
1  | 300  |   25
2  | 302  |   35
3  | 100  |   50
4  | 100  |   30
5  | 200  |   40
6  | 103  |   45

Here's my query so far:

SELECT code, AVG(rating) FROM table GROUP BY code;

I want to average the rating of all codes greater than 300. I tried setting an IF on the SQL query but it seems it doesn't work. Also tried having two group BYs but it won't work. I want to achieve this result:

code         |  AVG (rating)
300+ (alias) |  30
100          |  40
200          |  40
103          |  45

Upvotes: 2

Views: 258

Answers (4)

Sagar Gangwal
Sagar Gangwal

Reputation: 7937

SELECT CODE,AVG(rating) FROM
(
SELECT CASE WHEN code >= 300 THEN '300+' ELSE CODE END AS CODE, rating FROM table 
)Z
GROUP BY code;

You can try the code above.

Upvotes: 3

Rubin Porwal
Rubin Porwal

Reputation: 3845

According to description mentioned in above question please try executing following SQL select query as a solution to it.

 select code,avg(rating) from table where code < 300 group by code  
 union 
 select code,avg(rating) from table where code >= 300

Upvotes: 2

Blank
Blank

Reputation: 12378

Use case when in group by:

select
    case when code >= 300 then '300+' else code end as code,
    avg(rating) as `AVG (rating)`
from yourtable
group by case when code >= 300 then 300 else code end
order by `AVG (rating)`

Here is a demo in SQLFiddle.

Note: this will work in MySQL, but as 300+ is string, so more rigorous way should cast code to varchar first:

select
    case when code >= 300 then '300+' else concat(code, '') end as code,
    avg(rating) as `AVG (rating)`
from yourtable
group by case when code >= 300 then '300+' else concat(code, '') end
order by `AVG (rating)`

Upvotes: 3

Mureinik
Mureinik

Reputation: 311143

You could use a case expression to group together all the codes greater than 300:

SELECT   CASE WHEN code >= 300 THEN '300+'
            ELSE CAST(code AS CHAR)
         END,
         AVG(rating) 
FROM     table 
GROUP BY CASE WHEN code >= 300 THEN '300+'
            ELSE CAST(code AS CHAR)
         END

Upvotes: 3

Related Questions