SQL Server Performance Forum – Threads Archive
Group by – with bit fields
I have a table where I need to group by a field, and some of the columns are defined as a bit. What is the best way to handle this situation, or am I better off changing the data types?Bit is a totally valid data type – it’s the best option when you need to store true/false. What’s your problem here?
Why change data type.You can use the group for bit column
select Discontinued,count(*) AS cou from dbo.Products group by Discontinued —————————————-
http://dineshasanka.blogspot.com/
I never said bit was not a valid data type. I am not grouping by any of the bit fields, but I need to include them in the result set. Of course, I cannot do a min or max on these fields…
Actually, I think I found the answer here: http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=2290 I assumed converting inside of the min or max wouldn’t work, but maybe it does…
MIN or MAX would have a much bigger implication on a bit column than on a date column, as there are just two values, and TRUE or FALSE is much more than a more-or-less random value for a set of rows. There was a posting on that thread with an actual SQL error message when doing MAX on a bit column, so it seems as if it cannot be done. Either you need the aggregate results with the bit column in the GROUP BY clause, or you don’t need to include it at all. If you’re only aggregating rows where the bit column is true (or false, for that matter) then include the column only in the HAVING or WHERE clause, and not in the SELECT or GROUP BY lists.
That may work in some scenarios, but not in this one. Anyway, converting inside of the aggregate function works. MAX(CAST(tbl.Bitfield As tinyint))
Let me put it this way: whichever workaround you choose, the MAX(BitField) expression will only tell you whether any row covered by the query has a 1 on the bit column, or not. Why the requirement to have it in the resultset?
It’s a table that holds flags indicating whether to perform a certain action. That is exactly what I want. I want to know if any row has a 1 on the bit column. I only don’t perform the action if all of them are false… There are also multiple bit values (a lot of them) so it is better than doing separate queries.
quote:I only don’t perform the action if all of them are false…
So that’s a check that you do BEFORE running this query? Then you can assume that MAX(BitField) is 1.
I am not sure what you are getting at, but the query is working perfectly for me. I have tested every possible combination, and it is giving me the exact results that I need. Thanks.
Sorry for being a little obstinate – it is Friday after all.[<img src=’/community/emoticons/emotion-5.gif’ alt=’
‘ />]<br /><br />I just hate to see workarounds for non-issues. If you already know there are rows with BitColumn = 1, then why not simply return a 1 on that column, instead of MAX(CAST(….)).
I don’t know if there are rows with BitColumn = 1. If the result comes back 0, then all of them were 0. If the result comes back as 1, then at least one of the rows had a 1. It’s not a workaround at all…
I was reading your comment "I only don’t perform the action if all of them are false… " – that’s why I was assuming you already <b>knew</b> there would be at least one row where the bit was 1.[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]
Yes, but there are multiple bit values in the result set. All may be false for one of the columns, but I still need the results for the rest of the columns.
Gotcha – at least one for each grouping in the aggregated data. Should have thought of that before making a nuisance of myself![<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]
]]>