Re: SQL: Multiple counting

Marco Shaw schrieb:
> 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
>
>

You can use that fact, that the count of values containing 1 in a dataset containing only 1 and 0 is equal to sum of that values.

Best regards

Maxim Received on Fri Jul 29 2005 - 09:13:29 CDT