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: SQL question

Re: SQL question

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Fri, 14 Jan 2000 12:00:50 -0500
Message-ID: <iblu7sgtk1jpegpbp03vv17bm0lef45onl@4ax.com>


On Fri, 14 Jan 2000 10:38:47 -0500, reddy <greddy_at_att.com> wrote:

>Hello everyone,
>
>I need to some help on a sql stmt which counts the number of records in
>monthly tables for each primary key record:
>
>Here is the scenario:
>
>We have monthly tables to store transaction records for each month(eg
>tran_1999_07, tran_1999_08 etc, the structure of the monthly tables are
>the same). One of the fields in the table is verver which can be
>repeated.
>
>The data may look like this:
>
>Tran_1999_07
>Tran_1999_08
>-----------------
>-----------------
>verver tran_date adsys_index verver
>tran_date adsys_index
>1 7/1/99 1001
>1 8/1/99 1001
>2 7/2/99 1002
>2 8/2/99 1002
>1 7/2/99 1003
>1 8/2/99 1003
>1 7/3/99 1004
>1 8/3/99 1004
>2 7/3/99 1005
>2 8/3/99 1005
>
>I want to count the total number of records for each verver in both the
>tables-I want the result to look like
>
>verver count(*)
>1 6
>2 4
>
>I tried using
>select verver, count(*) from tra_1999_07
>union all
>select verver, count(*) from tra_1999_08
>order by 1
>
>But this repeating the ververs for each table.

how about this...

select a.verver, count(*)
  from ( select * from tra_1999_07

          union all
         select * from tra_1999_08 ) A

 group by a.verver
/

hope this helps.

chris.

>
>I appreciate any help on this.
>
>Thanks
>-Reddy
>
>
>

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.



Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jan 14 2000 - 11:00:50 CST

Original text of this message

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