when more than one condition satisfies, how to group them to calculate average

Question

I'm using Oracle database and want to calculate the mean product price for different regions.

Example:

prod,amt,price
X,100,1
X,180,2
X,250,3
Y,90,2
Y,170,3
Y,280,3

Here product X is sold for 100 in one region and 180 in another region..etc.

Now, for some analytics these are grouped into different ranges which are overlapping and I need to calculate the mean of the price based on the range

Required output is

prod,rang(Amt),mean(price),
X,[0-200],1.5,
X,[150-300],2.5,
Y,[0-200],2.5,
Y,[150-300],3,

Note that there are many ranges and I have given just 2 for clarity..

I tried like below, but the case is matching first condition and just giving one value for X for [0-200], where I need 2 records

select prod, amt, price, 
case 
when amt between 0 and 200 then amt
when amt between 150 and 300 then amt
end as rng
from tablea

How to get 2 records for a match in case statement?.

score 2 · Accepted Answer · answered Jan 23 '19 at 06:18

You can use conditional aggregation and then unpivot as

   select prod_a as "Prod",
          prod as "Range of Amounts",
          value as "Mean Price"
     from  
            (
            with tablea(prod,amt,price) as
            (
              select 'X',100,1 from dual union all
              select 'X',180,2 from dual union all
              select 'X',250,3 from dual union all
              select 'Y',90, 2 from dual union all
              select 'Y',170,3 from dual union all
              select 'Y',280,3 from dual 
            )
            select prod as prod_a,  
            avg(case when amt between 0 and 200 then price end) as avg_0_200,
            avg(case when amt between 150 and 300 then price end) as avg_150_300
            from tablea a
            group by prod
             ) b  
    unpivot(value for prod in(avg_0_200,avg_150_300))
    order by prod_a;

   Prod  Range of Amounts   Mean Price
   ----  ----------------    ----------
   X     AVG_0_200              1.5
   X     AVG_150_300            2.5
   Y     AVG_0_200              2.5
   Y     AVG_150_300            3

Rextester Demo

Thanks Barbaros.. it works.. any idea why in Zaynul's answer .. for X,150-300, the output is 2.5 not 3? — stack0114106, Jan 23 '19 at 06:45
also.. give me some idea on how to generate the text pattern ````avg(case when amt between 0 and 200 then price end) as avg_0_200,```` for many such ranges using the SQL? — stack0114106, Jan 23 '19 at 06:47
@stack0114106 since prod 'X' has two rows with `amt` values 180,250 in the range 150-300, those have prices 2,3 respectively, and average is (2+3)/2 = 2.5. Unfortunately, you cannot generate such patterns dynamically for pivot or unpivot structures yet. Such case is possible for `xml pivot`. — Barbaros Özhan, Jan 23 '19 at 11:28

score 1 · Answer 2 · edited Jan 23 '19 at 10:14

1

Make a two queries with single condition and union them. This is not a perfect solution but will work.

SELECT prod, '0-200' rang, AVG( price )
FROM   table_name
WHERE  amt BETWEEN 0 AND 200
GROUP BY prod
UNION ALL
SELECT prod, '150-300' rang, AVG( price )
FROM   table_name
WHERE  amt BETWEEN 150 AND 300
GROUP BY prod

edited Jan 23 '19 at 10:14

MT0

143,790
11
59
117

answered Jan 23 '19 at 05:50

jay sedani

57
9

1

@Richard This is a valid solution. It could be improved with a code example such as `SELECT prod, '0-200' rang, AVG( price ) FROM table_name WHERE price BETWEEN 0 AND 200 GROUP BY prod UNION ALL SELECT prod, '150-300' rang, AVG( price ) FROM table_name WHERE price BETWEEN 150 AND 300 GROUP BY prod`. – MT0 Jan 23 '19 at 10:11
@MT0 this was reviewed when there was no code in it. At that point it did not provide an answer (but would be a valid comment to the question as a suggested approach). – Richard Jan 23 '19 at 10:54

when more than one condition satisfies, how to group them to calculate average

2 Answers2