CODEWITHSUNDEEP
sqloracle-databasegroup-byaggregate-functions
user2010955
user2010955

Reputation: 4011

Oracle group by with different condition for each aggregate function

I'm wondering if I can perform a group by, and select a different subset of rows for each aggreate function. Example:

select order, count(*), sum(*), avg(*)
from table_orders
group by order

I'd like to restrict the rows used by each aggreate function using different conditions, like

count(*) where order_price > 100
sum(*) where order_id < 200
avg(*) where other_condition

Thank you all!

Upvotes: 4

Views: 1667

Answers (3)

stefan
stefan

Reputation: 2252

Suppose you have the following ORDERS table:

create table orders ( id, order_price )
as
select level, level * 100
from dual
connect by level <= 12 ;


ID  ORDER_PRICE  
1   100          
2   200          
3   300          
4   400          
5   500          
6   600          
7   700          
8   800          
9   900          
10  1000         
11  1100         
12  1200

Using CASE ... (as @fa06 suggested) will give you the following result - which is probably not quite what you initially wanted:

--
-- eg 
-- {1} count orders that have an order_price greater than 500
-- {2} find the sum of all orders that have an order_price greater than 900
-- {3} find the average value of orders that have an order_price < 300 
--
select 
  id
, count( case when order_price > 500 then id end )         count_
, sum( case when order_price > 900 then order_price end )  sum_
, avg( case when order_price < 300 then order_price end )  avg_
from orders
group by id
;

-- result
ID  COUNT_  SUM_  AVG_  
1   0       NULL  100   
6   1       NULL  NULL  
11  1       1100  NULL  
2   0       NULL  200   
4   0       NULL  NULL  
5   0       NULL  NULL  
8   1       NULL  NULL  
3   0       NULL  NULL  
7   1       NULL  NULL  
9   1       NULL  NULL  
10  1       1000  NULL  
12  1       1200  NULL  

12 rows selected.

You could use the CASEs in an inline view, and aggregate its result set like so:

select 
  count( gt500 )
, sum ( gt900 )
, avg ( lt300 )
from (
  select 
    id
  , case when order_price > 500 then 1 end            gt500
  , case when order_price > 900 then order_price end  gt900
  , case when order_price < 300 then order_price end  lt300
  from orders
)
;

-- result
COUNT(GT500)  SUM(GT900)  AVG(LT300)  
7             3300        150

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142733

Would something like this do what you're looking for?

SQL> select e.deptno,
  2    (select count(*)
  3     from emp e1
  4     where e1.job = 'SALESMAN'
  5       and e1.deptno = e.deptno
  6    ) v_count,
  7    (select sum(e2.sal)
  8     from emp e2
  9     where e2.comm is not null
 10       and e2.deptno = e.deptno
 11    ) v_sum,
 12    (select round(avg(e3.sal))
 13     from emp e3
 14     where e3.ename < 'KING'
 15       and e3.deptno = e.deptno
 16    ) v_avg
 17  from emp e
 18  group by e.deptno;

    DEPTNO    V_COUNT      V_SUM      V_AVG
---------- ---------- ---------- ----------
        30          4       5600       1800
        20          0        800       2358
        10          0                  2450

SQL>

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try below - using case when expression

select order, count(case when  order_price > 100 then order end), 
sum(case when order_id < 200 then order_price end), 
avg(case when other condition then order_price end)
from table_orders
group by order

Upvotes: 5

Related Questions