SQL or operator how to use with having

Question

i have a table which i am joining with with operator. I can have 2 combinations id that table FDEL - 1 or 0 and FDVE 1 or 0, what i would like to do is to dispay if - item has fdve, or item has fdel (and count) but it doesnt work (i can see all fdve, or all fdel)

select
    lpad(purchase_id,10,0) as purchase_id,
   sum(has_label_fdel) as FDEL_count,
    case when  LABELS   like '%FDVE%' then 1 else 0 end as HAS_LABEL_FDVE,
    sum(has_label_fdve) as FDVE_count
 from 
    "SRC_ORACLEIWP"."PURCHASE_ANALYSIS_RULES"
    group by
    lpad(purchase_id,10,0),has_label_fdve
    
having  FDVE_count>0 -- FDEL_count>0  

Answer 1

You want one resut row per product, so group by product only. Use SUMfor counting and MAX for the aggregated yes/no.

select
  lpad(purchase_id,10,0) as padded_purchase_id,
  max(has_label_fdve) as has_labels_fdve,
  sum(has_label_fdve) as fdve_count,
  max(has_label_fdel) as has_labels_fdel,
  sum(has_label_fdel) as fdel_count
from src_oracleiwp.purchase_analysis_rules
group by padded_purchase_id
having has_labels_fdve = 1
    or has_labels_fdel = 1
order by padded_purchase_id;

I've changed your alias names slightly, so they are digfferent from the columns you have (because such ambiguities can sometimes lead to problems).

The check on labels like '%FDVE%' is unnecessary, because you already have the has_label_fdve flag, which is always 0 or 1. Or so it seems. If the flags can be null, use COALESCE on them or do use LIKE expressions.

If you don't have has_label_fdve and has_label_fdel yet, use the labels column instead:

select
  lpad(purchase_id,10,0) as padded_purchase_id,
  max(case when labels like '%FDVE%' then 1 else 0 end) as has_labels_fdve,
  sum(case when labels like '%FDVE%' then 1 else 0 end) as fdve_count,
  max(case when labels like '%FDEL%' then 1 else 0 end) as has_labels_fdel,
  sum(case when labels like '%FDEL%' then 1 else 0 end) as fdel_count
from src_oracleiwp.purchase_analysis_rules
group by padded_purchase_id
having has_labels_fdve = 1
    or has_labels_fdel = 1
order by padded_purchase_id;