0

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?

halfer
  • 19,824
  • 17
  • 99
  • 186
Metafaniel
  • 29,318
  • 8
  • 40
  • 67
  • 1
    `GROUP BY ad_st_id_state` – Gordon Linoff Nov 07 '14 at 20:42
  • It's not at all clear why you are using nested `IF()`'s. Can you post some data from each of your tables - not the result from the query. – Taryn Nov 07 '14 at 21:00
  • @GordonLinoff I've tried this before, but I don't get the results I need. What I need is to get the average price of every brand within each country's state. So I first group by state and then by brand and I get the correct results, except that I need one row for every state as stated above. If I just do `group by ad_st_id_state` , all brands prices are mixed by state. I wish I was clear. Thanks for your comment – Metafaniel Nov 07 '14 at 23:15
  • @bluefeet I'm doing the `if` stuff because this data is being pivoted and in order to do that I need an aggregate function to pivot. – Metafaniel Nov 07 '14 at 23:20
  • @bluefeet You kindly helped me in this similar question: http://stackoverflow.com/questions/26021866/mysql-dynamic-transpose-pivot-of-a-table BUT this one is different because I need to group by two fields in order to get the results I need. Please help =( Thanks! – Metafaniel Nov 07 '14 at 23:21

1 Answers1

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • HEY IT WORKS! I even added the `round()` But can I bother you a little further? I didn't understand why this worked. My query was indeed different but I tried to `groyp by ad_st_id_state` and I didn't get the desired result. What's the difference? An `if` is a kind of `case` (let's think about it this way). Can you please tell me why you saw my error? What was clear for you it was wrong? THANKS! – Metafaniel Nov 07 '14 at 23:47
  • @Metafaniel . . . I'm pretty sure it was the nested `if`. The outer `if` chose a the value of `id_brand` from an indeterminate row. If it didn't match, it returned `NULL`. In an aggregation query, all columns in the `select` should be in the `group by` or in aggregation functions, unless you really, really know what you are doing. – Gordon Linoff Nov 07 '14 at 23:50
  • I really really didn't know what I was doing haha Thanks a lot. I still need more practice pivoting tables! =) – Metafaniel Nov 07 '14 at 23:58