MySQL - can't group by correctly in pivoted table

Question

I'm pivoting a query I had and now I have the following query:

select
ad_st_id_state,
round(IF(id_brand = 72, avg(if(id_brand = 72, vp_retail, null)), null),2) AS 'PRODUCT 1',
round(IF(id_brand = 75, avg(if(id_brand = 75, vp_retail, null)), null),2) AS 'PRODUCT 2'

from sf_product
join sf_brand on fa_ba_id_brand = id_brand

where vi_pr_id_proyect = 5

GROUP BY
id_brand, ad_st_id_state

The results I get are well calculated, by I got my resultset like this:

ad_st_id_state | PRODUCT 1 | PRODUCT 2
7   24.05   null
19  23.91   null
23  23.38   null
7   null    27.37
19  null    24.68
23  null    24.46
7   null    null
19  null    null
23  null    null
7   null    null
19  null    null
23  null    null

But I'd like the result set to be:

ad_st_id_state | PRODUCT 1 | PRODUCT 2
7   24.05   27.37
19  23.91   24.68
23  23.38   24.46

I've been trying to get this by modifying the group by in different combinations but I just can't do this. What am I doing wrong?

Answer 1

I would write the query like this:

select ad_st_id_state,
       avg(case when id_brand = 72 then vp_retail end) as Brand_72,
       avg(case when id_brand = 75 then vp_retail end) as Brand_75
from sf_product join
     sf_brand
     on fa_ba_id_brand = id_brand
where vi_pr_id_proyect = 5
group by ad_st_id_state;