Re: More SQL counting questions...

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 5 Aug 2005 08:36:02 -0700
Message-ID: <1123256162.846883.118530@g49g2000cwa.googlegroups.com>

This may not be the slickest solution but it works for the test data given. I just broke the problem into queries for part of the data and (outer) joined them. If there are more than 2 types of messages you will probably need to modify the solution since I derived the major error count as total count minus critical. But writing the query to sum 0 or 1 for critical and major in one query is easy with case/decode.

drop table A;
create table A (
 servername varchar2(10),
 severity number,

 log        number,
 notif      number);
insert into A values ('A',32,0,0);
insert into A values ('A',32,1,0);

insert into A values ('A',8,1,1);
insert into A values ('B',8,0,0);

UT1 > select drv.servername, drv.severity,

  2         nvl(sev.critical,0) critical,
  3         nvl(drv.severity,0) - nvl(sev.critical,0) as major,
  4         drv.log,
  5         drv.notif

  6 from (
  7 select servername, count(severity) as severity,   8 sum(log) log, sum(notif) notif   9 from A
 10 group by servername
 11 ) drv,
 12 (
 13 select servername, count(severity) as critical  14 from A
 15 where severity = 32
 16 group by servername
 17 ) sev
 18 where drv.servername = sev.servername(+)  19 /

SERVERNAME SEVERITY CRITICAL MAJOR LOG NOTIF ---------- ---------- ---------- ---------- ---------- ----------

A                   3          2          1          2          1
B                   1          0          1          0          0

HTH -- Mark D Powell -- Received on Fri Aug 05 2005 - 10:36:02 CDT