CODEWITHSUNDEEP
sql-servergroup-bybit
Marlon
Marlon

Reputation: 127

Cannot group by bit column

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?

Upvotes: 1

Views: 2377

Answers (2)

gbn
gbn

Reputation: 432722

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.

Upvotes: 2

KM.
KM.

Reputation: 103717

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)

Upvotes: 3

Related Questions