Re: Count in SQL

On 2005-07-28, Marco Shaw <marco_at_Znbnet.nb.ca> wrote:
> *SQL newbie alert*
>
> I have 2 tables, and I need to write some SQL to output counts of various
> entries.
>
> I've managed to kind of figure out how to proceed when only one thing needs
> to be counted:
>
> select
> to_char(count(*)) cnt, node.node_name
> from
> opc_node_names node,
> opc_act_messages act
> where
> act.node_id = node.node_id and
> act.severity = 1
> group by
> node.node_name
> order by
> count(*) DESC, node.node_name;
>
> That about outputs simply something like:
>
> 300 server_1
> 400 server_2
> etc.
>
> Now I need to also have a number similar to above, but also take into
> account totals in other columns of the tables.
>
> For example, above I just check a table for all the instances of server_name
> where severity=1, but I'll also need to count all the instances of
> server_name where severity=1 and column X or column Y have a value set (not
> null). The output might look like:
>
> server messages column Y column X
> -------- ---------- ---------- ----------
> server_1 300 200 100
>
> Note that X+Y=300 like the messages value.
>
> So how can I setup different 'counts' properly?
>
> Very hard to explain!

Very hard to give an answer, too!

You might be very lucky, however: count(col_x) only counts those row whose column col_x is not null.

hth
Rene

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