Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to get counts and sub_counts of a table with single query?

Re: How to get counts and sub_counts of a table with single query?

From: Allen Kirby <akirby_at_att.com>
Date: 1997/06/13
Message-ID: <33A18DF4.6ED@att.com>#1/1

Adm Tadj wrote:
>
> I'd like to built a VIEW on a SELECT statment which retrieves total
> counts and some other sub_counts which meet certain WHERE conditions.
> A row must be returned even there were zero sub_conts.
> Here is my query and it works as along as all its SELECTs return a
> value otherwise no rows get selected, I am aware of OUTER JOINs but it
> only works if there was only one table/view.
>
> SELECT a.col_a, a.tot_cont, b.sub_cont_b, c.sub_cont_c,.. FROM
> (SELECT col_a, COUNT(*) tot_cont FROM a_table
> GROUP BY col_a) a,
> (SELECT col_a, COUNT(*) sub_cont_b FROM a_table
> WHERE col_x BETWEEN 10 AND 20
> GROUP BY col_a) b,
> (SELECT col_a, COUNT(*) sub_cont_c FROM a_table
> WHERE col_y > 100
> GROUP BY col_a) c
> .
> .
> WHERE a.col_a = b.col_a AND a.col_a = c.col_a;

Here is a simple example, where you want a total count and a count of each distinct value of cola. This assumes you know ahead of time what the data values in cola will be. You should be able to construct something similar (but much more complicated) for your problem.

select

	sum(decode(col_a,'X',1,0)) "Number of Xs",
	sum(decode(col_a,'Y',1,0)) "Number of Ys",
	sum(decode(col_a,'Z',1,0)) "Number of Zs",
	count(col_a) "Total Records"

from table_a;

It's been a while and I did not test this, so beware. You might be able to combine this technique with a GROUP BY to get what you want. At least it's a start.

-- 
Allen Kirby			AT&T ITS Production Services
akirby_at_att.com			Alpharetta, GA.
Received on Fri Jun 13 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US