3 ms·
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: DECLARE @orders TABLE (
by nickpeterson 10y ago
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:
DECLARE @orders TABLE (
category VARCHAR(5),
express_delivered INT
);
INSERT INTO @orders (category, express_delivered) VALUES ( 'food', 1);
INSERT INTO @orders (category, express_delivered) VALUES ( 'food', 0);
INSERT INTO @orders (category, express_delivered) VALUES ( 'shoes',0);
INSERT INTO @orders (category, express_delivered) VALUES ( 'shoes',0);
INSERT INTO @orders (category, express_delivered) VALUES ( 'auto', 1);
INSERT INTO @orders (category, express_delivered) VALUES ( 'auto', 1);
INSERT INTO @orders (category, express_delivered) VALUES ( 'book', 1);
INSERT INTO @orders (category, express_delivered) VALUES ( 'book', 0);
SELECT
category,
CASE WHEN SUM(express_delivered) = 0 THEN 0 ELSE 1 END AS ever_been_express_delivered
FROM @orders
GROUP BY category
- netcraft 10y agoits 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.html http://www.postgresql.org/docs/9.1/static/datatype-boolean.h...
- aetherson 10y agoThat'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
- gary__ 10y agoSince 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 https://msdn.microsoft.com/en-GB/library/hh213574.aspx https://msdn.microsoft.com/en-GB/library/hh213574.aspx
- Pxtl 10y agoGoddamnit 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.