where-clausesqlanywherewhere-in

I need the query to only show count(sku) > 10

How can I add up the totals from a column I've already asked to sum up?

select 
--distinct  batch_date, bol_nbr, cur_opr, lane_nbr, size_code, sku 
loc_code, ship_lab, size_code, sku, COUNT(SKU)
from sherwin.prdinv
where sku IN ('B28W8030', 'B20W12651', 'A87W1351', 'B31W4651', 'A6W151') and (loc_code like 'H0%' or (loc_code like 'B%' ))
and REGEXP_LIKE((SUBSTR(loc_code,3)), '^[[:digit:]]+$')
group by loc_code, ship_lab, SKU, size_code
ORDER BY Loc_code asc;

I tried: where sku IN ('B28W8030', 'B20W12651', 'A87W1351', 'B31W4651', 'A6W151') and (loc_code like 'H0%' or (loc_code like 'B%' ) and (count(sku) > 10)

I wanted the report to only show me sku's where the value was greater than 10

Solution

  • You just add the HAVING clause to your query.

    select distinct  batch_date, bol_nbr, cur_opr, lane_nbr, size_code, sku 
loc_code, ship_lab, size_code, sku, COUNT(SKU)
from sherwin.prdinv
where sku IN ('B28W8030', 'B20W12651', 'A87W1351', 'B31W4651', 'A6W151') and (loc_code like 'H0%' or (loc_code like 'B%' ))
and REGEXP_LIKE((SUBSTR(loc_code,3)), '^[[:digit:]]+$')
group by loc_code, ship_lab, SKU, size_code
having COUNT(SKU) < 10
ORDER BY Loc_code asc;