Counts for lots of boolean fields in one sql query?

Question

Not sure exactly how to explain this but Imaging you have a table with lots of boolean fields like this......

Table: Cars

Columns:

Automatic: boolean

Silver: boolean

American: boolean

Noisy: boolean

Smelly: boolean

fast: boolean

(silly fields and most of them wouldn't be bools in reality but just an example)

What I need to do is produce a list of these fields with a number of search results next to each one, so if there were 100 silver cars in the database and 57 American cars the list might look a bit like this...

Automatic: (150)

Silver (100)

American (57)

Noisy (120)

Smelly (124)

fast (45)

So, it's basically like a list of filters, if the user clicks "silver" they will narrow the search down to only show silver cars and they know they will get 100 results. The numbers next to all the other filters will then decrease because we've filtered out all cars that aren't silver.

Counting the occurences of one field would be easy....

SELECT COUNT(*) FROM CARS WHERE Automatic = true;

... would give me the first row for example. But I don't want to have to do one SQL statement for each filter as there could be over 30 of them. I've seen plenty of sites do this so it must be easier than I think it is.

Any help would really be appreciated :)

Jon

Answer 1

Assuming the bit is 1/0 then you can SUM instead of COUNT:

SELECT SUM(Automatic) as Automatic, SUM(Smelly) as Smelly, SUM(American) as Japanese FROM ...

Answer 2

If it's not already 1/0, then, based on whatever the truevalue is, you can do this:

  Select
     Sum(Case When Automatic = truevalue Then 1 Else 0 End) as Automatic,
     Sum(Case When Smelly = truevalue   Then 1 Else 0 End) as Smelly,
     Sum(Case When American = truevalue   Then 1 Else 0 End) as American,
     Sum(Case When Noisy = truevalue   Then 1 Else 0 End) as Noisy
  From Table

Answer 3

With sql server 2008 when trying to sum a field of data type bit the following error ocures:

Msg 8117, Level 16, State 1, Line 10

Operand data type bit is invalid for sum operator.

so you can try this:

SELECT SUM(CAST(Automatic AS TINYINT)) as Automatic, SUM(CAST(Smelly AS TINYINT)) as Smelly, SUM(CAST(American AS TINYINT)) as Japanese FROM YourTable