Re: Count in SQL

Marco Shaw 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!
>
> Marco

Post the DDL for the table.

But something like this is possible:

SELECT col1, COUNT(*)
FROM t
WHERE col1 = 1
GROUP BY col1
UNION ALL
SELECT col1, COUNT(*)
FROM t
WHERE col1 = 2
GROUP BY col1;

Oh and drop the TO_CHAR from your example.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)