Reputation: 127
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
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
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