SQL: Multiple counting

I want to produce a report. Here's a sample table I want to report on:

server_name severity log_only notification_only

------------  -------- --------  ----------------
serverA        critical    0           0
serverA        critical    1           0
serverA        major     1           1
serverB        major     0           0

The last two columns can only be a zero or one.

I want a report written in SQL that produces something like:

server_name no of messages log_only notification_only
------------ --------------- --------- ----------------

serverA        3                       2             1
serverB        1                        0            0

In the report, the server_name will group all the serverA entries together, as the original table has 3 entries for serverA, the 2nd column will count the number of entries and report 3 entries in the table for serverA for 'no of messages', the 3rd column will report all of the serverA entries where the value in the log_only column of the original table is set to '1'. The 4th column will represent the count where the notification_only value of the original table is set to '1'.

It is this combination of multiple counting and combining that I do not know where to start.

I've been able to create a report where I can properly count the server_name and # of messages to produce a report such as:

server_name no of messages
------------ ---------------

serverA        3
serverB        1

But I have not been able to figure out how to extend that to keep totals of the other columns when they match '1'.

Marco Received on Fri Jul 29 2005 - 09:08:38 CDT