Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL question
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
hope this helps.
chris.
>
>I appreciate any help on this.
>
>Thanks
>-Reddy
>
>
>
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
![]() |
![]() |