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

Dealing with BIT type: Casting BIT to INT

When dealing with BIT columns, one might want to perform operations that can't be done directly. For such scenarios, one can convert the BIT to an INT, providing you with more flexibility:

SELECT 
  GroupColumn, 
  SUM(CONVERT(INT, BitColumn)) AS IntValue
FROM 
  TableName
GROUP BY 
  GroupColumn;


This will enable you to perform arithmetic operations on BIT columns - like calculating sums, averages, and so on - without having to add the BIT column to your GROUP BY.

Beyond inclusion: Clever use of aggregate functions

When you want to extract more information without including BIT columns in GROUP BY, aggregate functions can be your best friend:

• SUM: Shows the total 1s in the bit column.
• AVG: When converted to INT, gives average value which could help understand the distribution of 1s to 0s.
• COUNT: By using along with a CASE statement, can give a count of either 0s or 1s.

SELECT 
  GroupColumn, 
  SUM(CONVERT(INT, BitColumn)) AS OnesCount -- like building one's confidence 💪 
FROM 
  TableName
GROUP BY 
  GroupColumn;


Here, OnesCount gives the total number of 1s within each group. So, it's like saying, "One for all, all for one".

Non-aggregate selection with ROW_NUMBER()

Sometimes, you might need to bypass the aggregation and grab the actual BIT value. In such cases, the ROW_NUMBER() function can come to your rescue. It will sequentially number each row, allowing you to select and filter rows without a GROUP BY:

SELECT * FROM (
    SELECT 
      GroupColumn, 
      BitColumn,
      ROW_NUMBER() OVER(PARTITION BY GroupColumn ORDER BY (SELECT NULL)) AS rn
    FROM 
      TableName
) AS SubQuery
WHERE rn = 1;


And just like that, you've got the first of every group, that too without needing to include the BIT column in the GROUP BY. That's like winning bingo without buying a ticket 🎉.