Applying the Min Aggregate Function to a Bit Field

Applying the MIN aggregate function to a BIT field

Since there are only two options for BIT, just use a case statement:

SELECT CASE WHEN EXISTS (SELECT 1 FROM ....) THEN 1 ELSE 0 END AS 'MinBit'
FROM ...
WHERE ...

This has the advantage of:

• Not forcing a table scan (indexes on BIT fields pretty much never get used)
• Short circuiting TWICE (once for EXISTS and again for the CASE)

It is a little more code to write but it shouldn't be terrible. If you have multiple values to check you could always encapsulate your larger result set (with all the JOIN and FILTER criteria) in a CTE at the beginning of the query, then reference that in the CASE statements.

SQL Aggregate bit columns to single bit result

I think casting to an int is probably best overall as there are no bitwise aggregates, anything else will also be "hacky".

For fun this should work without casting the bit fields;

select
  EntityId,
  1 ^ count(distinct nullif(Submitted, 1)),
  1 ^ count(distinct nullif(Reviewed, 1)),
  count(distinct nullif(Query, 0))
from t
group by EntityId

Aggregate logical AND / OR for columns with type 'bit'

MIN and MAX functions can be used:

SELECT
  category,
  MAX(CONVERT(tinyint,isRed)) isAnyRed,
  MIN(CONVERT(tinyint,isBlue)) isAllBlue
FROM
  #Example
GROUP BY
  category

But you have to convert bit to some numeric value (tinyint), as MIN and MAX work only with numbers, not booleans.

Is there an Aggregate function that functions similarly to STUnion()?

SQL Server offers some aggregate methods on geometries, including UnionAggregate and CollectionAggregate that operate on more than 2 shapes.

From the UnionAggregate example :

-- Setup table variable for UnionAggregate example 
DECLARE @Geom TABLE 
( 
shape geometry, 
shapeType nvarchar(50) 
);
 
INSERT INTO @Geom(shape,shapeType) 
VALUES
('CURVEPOLYGON(CIRCULARSTRING(2 3, 4 1, 6 3, 4 5, 2 3))', 'Circle'), 
('POLYGON((1 1, 4 1, 4 5, 1 5, 1 1))', 'Rectangle'); 

-- Perform UnionAggregate on @Geom.shape column 
SELECT geometry::UnionAggregate(shape).ToString() 
FROM @Geom;

This produces

CURVEPOLYGON (COMPOUNDCURVE (
  (1 1, 4 1, 4.0000000000000071 1.0000000000000218), 
  CIRCULARSTRING (4.0000000000000071 1.0000000000000218, 
    5.4142135623730905 1.5857864376269268,
    6 3, 
    5.4142135623730905 4.4142135623730905, 
    4.0000000000000071 4.9999999999999947), 
  (4.0000000000000071 4.9999999999999947, 1 5, 1 1))
)

How to include BIT type column in SELECT part with out including it on the GROUP BY in T-SQL?

Put a CASE expression in there, or convert it to int:

IsActive = MAX(CASE WHEN IsActive=1 THEN 1 ELSE 0 END)

or,

IsActive = MAX(CONVERT(int,IsActive))

You should also be aware, obviously, that this means that the values in the ProductName, VendorName and IsActive columns in the result set may all come from different rows in the base table.

---

If you want those three columns to actually all be from the same row (and assuming SQL Server 2005 or later), you'd do something like:

;With Numbered as (
    SELECT *,ROW_NUMBER() OVER (
        PARTITION BY ProductID,VendorID
        ORDER BY /* Something appropriate, or if we just want random... */ newid()) as rn
    FROM ProductVendorAssoc
)
select
    ProductID,
    VendorID,
    ProductName,
    VendorName,
    IsActive
FROM Numbered where rn=1

Get MAX value of a BIT column

you can cast it to an INT, and even cast it back to a BIT if you need to

SELECT 
     sur.*
    ,CAST(MAX(CAST(bo.BOOL as INT)) AS BIT)
    FROM SURNAME sur              
    INNER JOIN BOOL bo
    ON bo.IDPERS = sur.IDPERS

During GroupBy I need to get only one result, but i am getting duplicates

SELECT CASE WHEN MIN(BitField+0) = 1 THEN 'True' ELSE 'False' END AS MyColumn
 FROM MyTable 

from the following link
Applying the MIN aggregate function to a BIT field

Modifying an Aggregate

If I understand you correctly you just need a row number partitioned by the patient and then a CASE expression to convert that into a multiplier. I've added an id column to the sample data to allow for an order by (which you need for a row number).

declare @Test table (id int identity(1,1), PName int, Rev int, CPT int, PPOSURG money);

insert into @Test (PName, Rev, CPT, PPOSURG)
values
(58903, 360, 29882, 4232.368),
(58903, 360, 29882, 4232.368),
(58903, 360, 29882, 4232.368),
(58903, 360, 29882, 4232.368);

with cte as (
    select *
        , row_number() over (partition by PName order by id) rn
    from @Test
)
select PName, Rev, CPT
    , cast(sum(PPOSURG * case rn when 1 then 1.00 when 2 then 0.50 else 0.25 end) as decimal(9,2)) Total
from cte
group by PName, Rev, CPT;

During GroupBy I need to get only one result, but i am getting duplicates

SELECT CASE WHEN MIN(BitField+0) = 1 THEN 'True' ELSE 'False' END AS MyColumn
 FROM MyTable 

from the following link
Applying the MIN aggregate function to a BIT field

---

Related Topics