Re: SQL question

Reddy;

This will work:

SELECT
   C.VERVER , COUNT(C.VERVER)
FROM
    (SELECT

       A.VERVER
     FROM
       Tran_1999_07 A

VERVER     COUNT(C.VERVER)
---------         ---------------
        1                   6
        2                   4

Of course, this will get increasingly ugly as you accumulate monthly tables. To address that, you may want to investigate dynamic SQL to construct the SELECT statement on the fly. By the way, this is a very good argument for having all your transaction records in one table and using the TRAN_DATE field to distinguish them.

Glen

"abacus" <prof_one_at_yahoo.com> wrote in message news: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 - 18:03:17 CET