Query with aggregation

Question

I'm using Oracle SQL, and i need help with a query.

I have the following table (table_A):

Account_ID (int)
Product_Name (varchar) 
A_Value (int)

There are 7 types of Products, and i shouldn't know their names in advance. Account_ID can has more than 1 Product, and Product can be more than 1 time for the same account.

I have another table (table_B):

Account_ID (int)
B_Value (int)

I need to write a query with the following output: Sum of A_Value for each type of Product_Name, divide by B_Value.

table_A for example:

Account_ID | Product_Name | A_Value
   111     |     A        |   5
   111     |     B        |   8
   111     |     D        |   2
   222     |     A        |   3
   222     |     A        |   10
   333     |     E        |   5
   333     |     E        |   8
   333     |     A        |   1

table_B for example:

Account_ID |   Value_B
     111   |    3
     222   |    2
     333   |    1

Output:

Account_ID |  Product_A       |  Product_B    |  Product_C |  Product_D    |  Product_E     | Product_F
   111     |    5/3 = 1.66    |    8/3 = 2.66 |    null    |   2/3 = 0.66  |    null        |   null
   222     |   (3+10)/2 = 6.5 |   null        |    null    |   null        |    null        |   null
   333     |   1/1=1          |   null        |    null    |   null        |  (8+5)/1 = 13  |    null

Is anybody know how to do that? I can do the calculation, but i don't know how to spread it in columns by products. Probably i had to do it in another way, but i don't know how.

Answer 1

You can get it via a pivot, then a join:

select b.account_id,
       product_a / b.value_b product_a,
       product_b / b.value_b product_b,
       product_c / b.value_b product_c,
       product_d / b.value_b product_d,
       product_e / b.value_b product_e,
       product_f / b.value_b product_f
from
(
  select *
  from
  (
    select *
    from table_a a
  )
  pivot (sum(a_value) for product_name in ('A' as product_a, 'B' as product_b, 'C' as product_c, 'D' as product_d, 'E' as product_e, 'F' as product_f))
) x, table_b b
where x.account_id = b.account_id
order by 1
;

This gives:

ACCOUNT_ID  PRODUCT_A   PRODUCT_B   PRODUCT_C   PRODUCT_D   PRODUCT_E   PRODUCT_F
    111         1,666...    2,666...    null        0,666...    null        null        
    222         6,5         null        null        null        null        null
    333         1           null        null        null        13          null

Answer 2

You can do this with conditional aggregation:

select a.account_id,
       sum(case when product_name = 'A' then a_value end) / max(value_b) as product_A,
       sum(case when product_name = 'B' then a_value end) / max(value_b) as product_B,
       . . .
       sum(case when product_name = 'F' then a_value end) / max(value_b) as product_F,
from table_a a join
     table_b b
     on a.account_id = b.account_id
group by a.account_id;