SQL question

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.

I appreciate any help on this.

Thanks
-Reddy Received on Fri Jan 14 2000 - 09:38:47 CST