How to select only values based on another column condition-CodePudding

I have a table having this format

id    col1     avg_amount_per_order
1     a        80
1     b        50
2     a        90
2     b        120
3     a        200
3     b        140
4     a        110
4     b        430

I want to select all the data, as long as if col1 = a, then the avg_amount_per_order must be >= 100. And for the same id, I only want to select rows, where avg_amount_per_order of 'b' is at least 2* avg_amount_per_order of 'a' in col1.

How to do that?

I tried this:

SELECT 
id
, col1
, avg_amount_per_order

from orders

WHERE
nr_orders >= 6
    AND CASE WHEN col1 = 'a' THEN avg_amount_per_order >= 100 ELSE avg_amount_per_order > 0 END

group by 
1,2,3

but this doesn't return the results like expected.

CodePudding user response：

This could be a simple way to reach a solution for your issue.

with tbl(id, col1, avg_amount_per_order) as (
values
(1, 'a', 80 ),
(1, 'b', 50 ),
(2, 'a', 90 ),
(2, 'b', 120),
(3, 'a', 200),
(3, 'b', 140),
(4, 'a', 110),
(4, 'b', 430)
)
select a.* 
from tbl a 
join tbl b on a.id = b.id and a.col1 != b.col1
WHERE (a.avg_amount_per_order  >= b.avg_amount_per_order * 2 
and  b.col1 = 'a' and b.avg_amount_per_order >= 100)
or  a.avg_amount_per_order *2 <= b.avg_amount_per_order

CodePudding user response：

Use the LEAD window function to get values and a and b together. Then an outer query to the the b>= 2*a:

select id, avg_amount_per_order_a, avg_amount_per_order_b
  from ( select id
              , avg_amount_per_order avg_amount_per_order_a 
              , lead(avg_amount_per_order) over (partition by id order by  col1) avg_amount_per_order_b  
           from testa 
       ) ab
 where  avg_amount_per_order_b >= 2 * avg_amount_per_order_a;

CodePudding user response：

with data as (
    select *,
        min(avg_amount_per_order) over (partition by id) filter (col1 = 'a') as avgA
        min(avg_amount_per_order) over (partition by id) filter (col1 = 'b') as avgB
    from T
)
select * from data
where avgA >= 100 and avgB >= 2 * avgA
-- ?? where col1 = 'a' and avgA >= 100 or col1 = 'b' and avgB >= 2 * avgA

I can't tell whether you want only one condition or both of them together.