Clearly the title is a bit of a mouth full, but I'll explain what we're going to do with an example data set:
Imagine this simple table of data...
| name | group | fatThin | tallShort | happySad |
|---|---|---|---|---|
| Alan | 1 | 1 = fat | 1 = tall | 1 = happy |
| Donald | 1 | 1 = fat | 0 = short | 1 = happy |
| Mary | 2 | 0 = thin | 0 = short | 1 = happy |
| Rodney | 2 | 0 = thin | 0 = short | 1 = happy |
| Sue | 3 | 0 = thin | 0 = short | 0 = sad |
| Ted | 3 | 0 = thin | 1 = tall | 0 = sad |
This table can be made and populated by the following SQL Server fragment:
create table [people] (
[name] varchar(16) primary key,
[group] int not null,
[fatThin] int not null,
[tallShort] int not null,
[happySad] int not null
)
insert into [people] ([name], [group], [fatThin], [tallShort], [happySad])values ('Alan', 1, 1, 1, 1)
insert into [people] ([name], [group], [fatThin], [tallShort], [happySad])values ('Donald', 1, 1, 0, 1)
insert into [people] ([name], [group], [fatThin], [tallShort], [happySad])values ('Mary', 2, 0, 0, 1)
insert into [people] ([name], [group], [fatThin], [tallShort], [happySad])values ('Rodney', 2, 0, 0, 1)
insert into [people] ([name], [group], [fatThin], [tallShort], [happySad])values ('Sue', 3, 0, 0, 0)
insert into [people] ([name], [group], [fatThin], [tallShort], [happySad])values ('Ted', 3, 0, 1, 0)
Using a combination of MIN, MAX, SUM and CASE we can achieve these bit wise operations. Examples:
Are all people in groups fat or thin?
select [group],
max(fatThin) as allFat,
min(fatThin) as allThin,
case min(fatThin) when 1 then 'fat' else 'thin' end as [result]
from [people]
group by [group]
group allFat allThin result
----------- ----------- ----------- --------
1 1 1 fat
2 0 0 thin
3 0 0 thin
Select Groups Where All Members are Thin and Happy
select [group]
from [people]
group by [group]
having max(fatThin)=0 and min(happySad)=1
group
-----------
2
Are All Fat People Happy?
select
case max(happySad) when 1 then 'yes' else 'no' end as [result]
from [people]
where fatThin=1
result
------
yes
