MYSQL different conditions in a single query

Hello

I have following columns in mysql table: rating1, rating2, price, cond, approved

Is it possible to select results like this:

select 
average rating1 + rating2 as total_rating,  
average rating1 as rating1,  
average rating2 as rating2,  
average price if cond = '1' as price_used  
average price if cond = '2' as price_new  
where approved = '1'

So far I have:

SELECT
(AVG(t.rating1开发者_运维知识库) + AVG(t.rating2)) / 2 AS total_rating  
AVG(t.rating1) AS rating1,  
AVG(t.rating2) AS rating2,  
---- price statements?? ----  
FROM t  
WHERE 1=1  
AND t.approved = '1'  

Many thanks and excuse me for my English

Try this:

SELECT (AVG(t.rating1) + AVG(t.rating2)) / 2 AS total_rating, 
                AVG(t.rating1) AS rating1, 
                AVG(t.rating2) AS rating2, 
                AVG(IF(cond='1', price, NULL)) price_used, 
                AVG(IF(cond='2', price, NULL)) price_new
FROM t
WHERE 1=1 
 AND t.approved = '1'

EDIT: Updated the query to get desired result.

Standard SQL, works across "all" major dbms:

select (avg(t.rating1) + avg(t.rating2)) / 2 as total_rating  
      ,avg(t.rating1) as rating1
      ,avg(t.rating2) as rating2
      ,avg(case when cond = '1' then price end) as price_used
      ,avg(case when cond = '2' then price end) as price_new
  from t   
 where t.approved = '1' 

I don't think doing an average on the If statement will work. But here is the syntax for the IF.

IF(condition, value_to_display_if_true, value_to_display_if_false)

So, for example, IF(1=1, 'true', 'false') would always display 'true' for this column because 1 does = 1.