Re:Seldomly Used SQL: The Bool Aggregate Functions
by nickpeterson on Thursday May 05, 2016 @01:20PM
(#11638011)
I don't know if postgres allows one to treat boolean as 0 and 1 natively but in t-sql, the equivalent would be something like:
[ Parent
| on HN ]
Re:Seldomly Used SQL: The Bool Aggregate Functions
by Pxtl on Thursday May 05, 2016 @11:02PM
(#11641401)
Goddamnit I hate how there is no true boolean datatype in t-sql. I can't write a simple predicate without comparing its output to 1 or 0.
Re:Seldomly Used SQL: The Bool Aggregate Functions
by gary__ on Thursday May 05, 2016 @04:11PM
(#11639316)
Since SQL Server 2012, you can use the lovely IIF statement, which is translated into a CASE behind the scenes.
So:
CASE WHEN SUM(express_delivered) = 0 THEN 0 ELSE 1 END AS ever_been_express_delivered
becomes:
IIF(SUM(express_delivered) = 0, 0, 1) AS ever_been_express_delivered
Re:Seldomly Used SQL: The Bool Aggregate Functions
by aetherson on Thursday May 05, 2016 @02:32PM
(#11638627)
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.
Re:Seldomly Used SQL: The Bool Aggregate Functions
by MSM on Thursday May 05, 2016 @03:00PM
(#11638846)
I 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.
Re:Seldomly Used SQL: The Bool Aggregate Functions
by nickpeterson on Thursday May 05, 2016 @03:24PM
(#11638995)
How about:
CASE WHEN MIN(express_delivered) = 0 THEN 0 ELSE 1 END AS always_been_express_delivered
Re:Seldomly Used SQL: The Bool Aggregate Functions
by netcraft on Thursday May 05, 2016 @01:50PM
(#11638262)
its one of my few aggravations with pgsql, but you cant use 0 and 1 unless they are quoted. The only non-quoted values you can use are TRUE and FALSE. http://www.postgresql.org/docs/9.1/static/datatype-boolean.h...