Re: SQL question

Try this :

select verver, sum(nb_verver) from
(select verver, count(*) nb_verver from tra_1999_07 group by verver union all
select verver, count(*) from tra_1999_08 group by verver) group by verver
order by 1

Regards

abacus <prof_one_at_yahoo.com> a écrit dans l'article <387F4007.A8BED225_at_att.com>...
> 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 - 16:53:50 CET