locked
Aggregate function for OR on a bit column RRS feed

  • Question

  • Hi,

    I have an issue, I have a result set as shown below
    what I want to do is group by PrivilageId and the IsGranted columns should get aggregate ORed.(just like sum)

    I tried out casting the bit column into int and doing a SUM, but this does not help my requirment.
    Can some one suggest a better way?


    PrivilageId PrivilageName RoomId           IsGranted GroupId
    1              Prev 1              1                  1         1
    2              Prev 2              1                  0         1
    3              Prev 3              1                 0         1
    1              Prev 1              1                 1         2
    2              Prev 2              1                  0         2
    3              Prev 3              1                  1         2

    Regards,
    Nairooz

    Thursday, October 22, 2009 11:30 AM

Answers

  • SUM aggregate function works if you convert the Boolean column to SMALLINT.

    USE tempdb; 
    
    SELECT ProductSubcategoryID, 
           ListPrice, 
           IsColor = CASE 
                       WHEN Color IS NULL THEN CAST('0' AS BIT) 
                       ELSE CAST('1' AS BIT) 
                     END 
    INTO   ProductPricing 
    FROM   AdventureWorks2008.Production.Product 
    
    SELECT   ProductSubcategoryID, 
             AvgPrice = AVG(ListPrice), 
             CountColor = SUM(convert(TINYINT,IsColor)) 
    FROM     ProductPricing 
    GROUP BY ProductSubcategoryID 
    ORDER BY ProductSubcategoryID DESC 
    
    GO 
    
    /* 
    ProductSubcategoryID  AvgPrice  CountColor 
    ........ 
    19  8.99  1 
    18  89.99  3 
    17  220.9292  14 
    16  631.4155  18 
    15  39.6333  0 
    14  780.0436  33 
    ........... 
    */ 
    DROP TABLE tempdb.dbo.ProductPricing 

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Thursday, October 22, 2009 11:47 AM
  • hm how about change the 0 in granted to null then do the sum?

    something like.

    SELECT privilageid, count(replace(isGranted, 0, NULL)) as CountIsgranted
    FROM Table
    Group BY PrivilageID
    if above doesn't produce the correct output than try
    SELECT privilageid, count(isGranted) as CountIsgranted
    FROM Table
    Group BY PrivilageID
    HAVING count(IsGranted) = 1

    btw sorry if i produced the same query as above cause I didn't refreshed the page before wrote it. :)


    Best Regards,
    Melissa Suciadi


    If you have found this post helpful, please click the 'Vote as Helpful ' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered ' link below. It helps others who experience the same issue in future to find the solution.

    Thursday, October 22, 2009 11:47 AM
  • I would use NULLIF, as it is pretty easy to use and helps here.

    DECLARE @sample TABLE(
    PrivilageId INT,
    PrivilageName VARCHAR(10),
    RoomId INT,
    IsGranted INT,
    GroupId INT
    )
    
    INSERT INTO @sample VALUES(1,'Prev 1',1,1,1);
    INSERT INTO @sample VALUES(2,'Prev 2',1,0,1);
    INSERT INTO @sample VALUES(3,'Prev 3',1,0,1);
    INSERT INTO @sample VALUES(1,'Prev 1',1,1,2);
    INSERT INTO @sample VALUES(2,'Prev 2',1,0,2);
    INSERT INTO @sample VALUES(3,'Prev 3',1,1,2);
    
    SELECT [PrivilageId],[PrivilageName], MAX(IsGrantedCnt) AS IsGrantedCnt
    FROM (
    SELECT [PrivilageId],[PrivilageName], COUNT(NULLIF(IsGranted,0)) AS IsGrantedCnt
    FROM @sample
    GROUP BY [PrivilageId],[PrivilageName],[GroupId]
    ) AS A
    GROUP BY [PrivilageId],[PrivilageName]
    

    http://jahaines.blogspot.com/
    • Proposed as answer by Brad_Schulz Thursday, October 22, 2009 4:11 PM
    • Marked as answer by Kalman Toth Thursday, October 22, 2009 4:39 PM
    Thursday, October 22, 2009 3:28 PM

All replies

  • Hi Nairooz,

    Its not clear exactly what the output should be. Can you clarify please?

    Does, the following not work:

    select privilageId, Isgranted, count(*)
    from tbl
    group by privilageId, IsGranted

    every day is a school day
    Thursday, October 22, 2009 11:45 AM
  • SUM aggregate function works if you convert the Boolean column to SMALLINT.

    USE tempdb; 
    
    SELECT ProductSubcategoryID, 
           ListPrice, 
           IsColor = CASE 
                       WHEN Color IS NULL THEN CAST('0' AS BIT) 
                       ELSE CAST('1' AS BIT) 
                     END 
    INTO   ProductPricing 
    FROM   AdventureWorks2008.Production.Product 
    
    SELECT   ProductSubcategoryID, 
             AvgPrice = AVG(ListPrice), 
             CountColor = SUM(convert(TINYINT,IsColor)) 
    FROM     ProductPricing 
    GROUP BY ProductSubcategoryID 
    ORDER BY ProductSubcategoryID DESC 
    
    GO 
    
    /* 
    ProductSubcategoryID  AvgPrice  CountColor 
    ........ 
    19  8.99  1 
    18  89.99  3 
    17  220.9292  14 
    16  631.4155  18 
    15  39.6333  0 
    14  780.0436  33 
    ........... 
    */ 
    DROP TABLE tempdb.dbo.ProductPricing 

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Thursday, October 22, 2009 11:47 AM
  • hm how about change the 0 in granted to null then do the sum?

    something like.

    SELECT privilageid, count(replace(isGranted, 0, NULL)) as CountIsgranted
    FROM Table
    Group BY PrivilageID
    if above doesn't produce the correct output than try
    SELECT privilageid, count(isGranted) as CountIsgranted
    FROM Table
    Group BY PrivilageID
    HAVING count(IsGranted) = 1

    btw sorry if i produced the same query as above cause I didn't refreshed the page before wrote it. :)


    Best Regards,
    Melissa Suciadi


    If you have found this post helpful, please click the 'Vote as Helpful ' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered ' link below. It helps others who experience the same issue in future to find the solution.

    Thursday, October 22, 2009 11:47 AM
  • Hi,

    The output I am expecting is

    1              Prev 1              1                1        
    2              Prev 2              1                0        
    3              Prev 3              1                1        

    I dont want the group id column in the result, but as u can see the IsGranted column as basically been ORed

    Thursday, October 22, 2009 12:39 PM
  • Tx for the quick reply,

    This is way I have presently written the query, however, the cast and the case does not look that good.
    Do we have a more efficent way of doing this, without cast and case?

    Tx.
    Thursday, October 22, 2009 12:44 PM
  • Hi Melissa ,
    Tx for the reply

    Any idea if this query is more efficent then the once pointed out by sqlusa?
    Thursday, October 22, 2009 12:50 PM
  • I would change REPLACE to CASE isGrantred WHEN 1 THEN 1 END.
    Thursday, October 22, 2009 2:35 PM
  • I would use NULLIF, as it is pretty easy to use and helps here.

    DECLARE @sample TABLE(
    PrivilageId INT,
    PrivilageName VARCHAR(10),
    RoomId INT,
    IsGranted INT,
    GroupId INT
    )
    
    INSERT INTO @sample VALUES(1,'Prev 1',1,1,1);
    INSERT INTO @sample VALUES(2,'Prev 2',1,0,1);
    INSERT INTO @sample VALUES(3,'Prev 3',1,0,1);
    INSERT INTO @sample VALUES(1,'Prev 1',1,1,2);
    INSERT INTO @sample VALUES(2,'Prev 2',1,0,2);
    INSERT INTO @sample VALUES(3,'Prev 3',1,1,2);
    
    SELECT [PrivilageId],[PrivilageName], MAX(IsGrantedCnt) AS IsGrantedCnt
    FROM (
    SELECT [PrivilageId],[PrivilageName], COUNT(NULLIF(IsGranted,0)) AS IsGrantedCnt
    FROM @sample
    GROUP BY [PrivilageId],[PrivilageName],[GroupId]
    ) AS A
    GROUP BY [PrivilageId],[PrivilageName]
    

    http://jahaines.blogspot.com/
    • Proposed as answer by Brad_Schulz Thursday, October 22, 2009 4:11 PM
    • Marked as answer by Kalman Toth Thursday, October 22, 2009 4:39 PM
    Thursday, October 22, 2009 3:28 PM
  • I was thinking that. But people seem to avoid NULLIF() for some reason.
    Thursday, October 22, 2009 3:52 PM
  • <<I was thinking that. But people seem to avoid NULLIF() for some reason.

    I think a part of the problem is that a lot of people just dont know about the NULLIF function.  When you look underneath the hood all the function does is a case expression.

    Scalar Operator(CASE WHEN [IsGranted]=(0) THEN NULL ELSE [IsGranted] END)

    http://jahaines.blogspot.com/
    Thursday, October 22, 2009 4:04 PM
  • Heh. Thanx Adam.
    Thursday, October 22, 2009 4:11 PM
  • Excellent suggestion with NULLIF.

    It's true that many people may not know about it, but it may also be the case that many people may not think they have a use for it.

    But this is a perfect example.

    --Brad (My Blog)
    Thursday, October 22, 2009 4:11 PM
  • True. Personally, i use it. Ease osf reading plays a role to. I guess it's a CASE by CASE decision.
    Thursday, October 22, 2009 4:23 PM
  • True. Personally, i use it. Ease osf reading plays a role to. I guess it's a CASE by CASE decision.

    Cute.  8^)


    --Brad (My Blog)
    Thursday, October 22, 2009 4:32 PM