How to group columns in sql table for counting

I'm not sure if there's an easy concept or pattern to do this, so I'll just have to describe it.

Let's say I have a table like this:

CallID (uniqueidentifier)
CallDateTime (datetime)
CallIssue (varchar(100))
Pollution (bit)
Violation (bit)
Accident (bit)
General (bit)

And I want to get counts on a report. For 30 different calls the counts would look like this:

Pollution: 4
Violation: 3
Accident: 2
General: 7
Pollution & Violation: 5
Pollution & Accident: 9

What would be happening is that if a call had both Pollution and Violation set they would be counted separately than calls having only Pollution or Violation.

Is there a way, other than a cursor, that I could do this?

We're using SQL 2开发者_Python百科005.

---

Something like this could do the counting for you.

select sum(case Pollution when 1 then 1 else 0 end) as Pollution,
       sum(case Violation when 1 then 1 else 0 end) as Violation,
       sum(case Accident when 1 then 1 else 0 end) as Accident,
       sum(case General when 1 then 1 else 0 end) as General,
       sum(case when Pollution = 1 and Violation = 1 then 1 else 0 end) [Pollution & Violation],
       sum(case when Pollution = 1 and Accident = 1 then 1 else 0 end) [Pollution & Accident]
from YourTable

---

I'm no SQL guru but off the top of my head

SELECT 
    SUM(CASE WHEN Pollution= 1 THEN 1 ELSE 0 END) AS Pollution, 
    SUM(CASE WHEN Violation= 1 THEN 1 ELSE 0 END) AS Violation, 
    SUM(CASE WHEN Accident= 1 THEN 1 ELSE 0 END) AS Accident,
    SUM(CASE WHEN Pollution & Violation= 1 THEN 1 ELSE 0 END) AS [Pollution_And_Violation], 
    SUM(CASE WHEN Pollution & Accident= 1 THEN 1 ELSE 0 END) AS [Pollution_And_Accident], 
FROM
dbo.Table

EDIT:

The same as Mikael, only using bitwise operations.

---

Simply doing a GROUP BY pollution, violation, accident, general should give you the rollups the way you want (which you can do a count(*) of). You can then read the grouped columns to figure out which rollup you're referring to.
Please note that if you are expecting to get this into some sort of display program, a cursor may be required in any case - however, in almost all cases, aggregation and sorting should be performed in the database layer.

Here's an example query:

SELECT pollution, violation, accident, general, count(*)
FROM callTable
GROUP BY pollution, violation, accident, general  

Doing things like setting the display to 'Pollution & Violation' can then be done - in your application code - by testing whether the relevant bit column is set to '1' or whatever, and displaying the count.