Re: SQL: Multiple counting

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: Fri, 29 Jul 2005 14:39:26 +0000 (UTC)
Message-ID: <dcdf2u$8kt$1@klatschtante.init7.net>

On 2005-07-29, Marco Shaw <marco_at_Znbnet.nb.ca> wrote:
> 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'.

What you want is called a pivot query. I have an example of it on my homepage at:
http://www.adp-gmbh.ch/ora/sql/examples/pivot.html

However, in your case, you don't have to use max(...) but sum(...) instead.

Something like

select
  servername,

  count(*)          no_of_messages,
  sum(log_only)     log_only,

  sum(notification) notification_only
from
  table_name
group by
  servername;

This should work assuming log_only and notification_only contain 0 and 1's exclusively.

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/

Received on Fri Jul 29 2005 - 09:39:26 CDT