Re: SQL: Multiple counting

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 29 Jul 2005 21:34:11 +0200
Message-ID: <dce0b8$2e2$04$1@news.t-online.com>

Marco Shaw schrieb:

>>The principle should work, independently what are the values in other
>>columns. You can be assisted with functions like DECODE or CASE, where
>>you can implement nearby every WHEN ... THEN logic and generate 1 resp.
>>0 for desired resp. undesired values, which can inturn be summarized...

>
>
> You kind of lost me, but I will reread a few dozen times, and then it might
> make sense to me.
>
> Marco
>
>

Small example:
Assumed you have your table extended by 1 column access_type ( whatever ) server_name severity log_only notification_only access_type

------------  -------- --------  ----------------------------------
serverA        critical    0           0                ftp
serverA        critical    1           0                ssh
serverA        major     1           1                  ssh
serverB        major     0           0                  ssh

Now you might have your report as previous, but you are interested in counting only servers of ssh access_type.

server_name no of messages log_only notification_only access_type

------------  ---------------  ---------  ----------------------------
serverA        3                       2             1        2
serverB        1                        0            0        1

In that case you take the query suggested by Rene and slightly extend it:

select

   servername,

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

   sum(notification) notification_only,
-- now comes additional column

   sum(case when access_type='ssh' then 1 else 0 end) access_type -- the same with decode
-- sum(decode(access_type,'ssh',1,0)) access_type from

   table_name
group by

   servername;

Best regards

Maxim Received on Fri Jul 29 2005 - 14:34:11 CDT