3 ms·
That's fine for an "or" aggregation, but it's harder for an "and" aggregation, in which case I guess you'd have to say: select category case when sum(exp
by aetherson 10y ago
That's fine for an "or" aggregation, but it's harder for an "and" aggregation, in which case I guess you'd have to say:
select
category
case when sum(express_delivered) = count(express_delivered) then 1 else 0 end as always_been_express_delivered
from etc.
- MSM 10y agoI think instead of trying to shoehorn the "AND" that he discusses in the article into a case statement you'd just look for each category to see if there has been a non expressed delivered package. SELECT category, ISNULL(NonExpress.NonExpress, 0) AS ever_been_non_express_delivered FROM @orders O OUTER APPLY ( SELECT TOP 1 1 as [NonExpress] FROM @Orders O2 WHERE O2.Category = O.Category AND express_delivered = 0 ) NonExpress GROUP BY category Edit: Changed the logic up, the original code's field was contradicting my logic.
- nickpeterson 10y agoHow about: CASE WHEN MIN(express_delivered) = 0 THEN 0 ELSE 1 END AS always_been_express_delivered