Cannot group by bit column

Question

In sql server 2005, i in the query builder, i select "Add group by" to automatically add the group by clause to all of the fields i selected. If one or more of those fields are a bit type, i get an error. Why is this? Is casting the column to TINYINT a good fix?

Answer 1

It looks like a limitation of that tool. if you just write the actual sql yourself in SQL Server Management Studio, it will work.

here is my test code:

CREATE TABLE Test2
(ID INT,
bitvalue bit,
flag char(1))
GO

insert into  test2 values (1,1,'a')
insert into  test2 values (2,1,'a')
insert into  test2 values (3,1,'a')
insert into  test2 values (4,1,'b')
insert into  test2 values (5,1,'b')
insert into  test2 values (6,1,'b')
insert into  test2 values (7,1,'b')

insert into  test2 values (10,0,'a')
insert into  test2 values (20,0,'a')
insert into  test2 values (30,0,'a')
insert into  test2 values (40,0,'b')
insert into  test2 values (50,0,'b')
insert into  test2 values (60,0,'b')
insert into  test2 values (70,0,'b')

select * from test2

select count(*),bitvalue,flag from test2 group by bitvalue,flag

OUTPUT

ID          bitvalue flag
----------- -------- ----
1           1        a
2           1        a
3           1        a
4           1        b
5           1        b
6           1        b
7           1        b
10          0        a
20          0        a
30          0        a
40          0        b
50          0        b
60          0        b
70          0        b

(14 row(s) affected)

            bitvalue flag
----------- -------- ----
3           0        a
3           1        a
4           0        b
4           1        b

(4 row(s) affected)

Answer 2

The tools don't allow some operations such as indexing or grouping on bit columns. Raw SQL does.

Note, you can't aggregate on bit columns. You have to cast first. Of course, averaging a bit columns is kinda pointless, but MAX/MIN is useful as a OR/AND spanning multiple rows.