I have requirement here to pick rows on certain condition. PFB the input and desired output.
Conditions: The row is valid if str type = 'S' and the item should not have str_type 'A' & 'D' associated.
Thanks for helping!
SELECT DISTINCT item, str_type, num
WHERE str_type = 'S'
AND item NOT IN (SELECT item FROM TEST WHERE str_type IN ('A','D'));
Conceptual - not tested...
This will be executed as a single table scan with aggregation - best that can be done unless other conditions can be applied to reduce the incoming set from the table. If there are other str_types for example they could be eliminated with an appropriate where clause.
This will not work properly if there are multiple S rows for an item since it will add the counts together. I added a counter to locate any cases where this is the case.
S_Count has been used to name the "count" column in the table supplied since "count" is a reserved word.
SUM(CASE WHEN str_type='S' THEN S_count ELSE 0) AS item_count,
SUM(CASE WHEN str_type='S' THEN 1 ELSE 0) AS S_item_row_count
GROUP BY item
SUM(CASE WHEN str_type='S' THEN 1 ELSE 0) > 0 AND
SUM(CASE WHEN str_type='A' OR str_type='D' THEN 1 ELSE 0) = 0;