Query Optimization Help needed

From: knez <knez_at_ix.netcom.com>
Date: Sat, 31 Oct 1998 18:29:33 -0500
Message-ID: <71g6g4$o0p@sjx-ixn3.ix.netcom.com>

I wrote a query that retrieves account numbers and items that the account has. Because the items are different, I pull back 1 acct# per item with the same acct#.

Example:    Acct#              Item
                    12001             apple
                    12001             pear
                    12001            grape
                    12005            apple
                    12009            pear
                    12009            grape

I run another query against the results of the first query that looks like this: select acct#, count(*) from query_one group by acct#.

This gives me exactly what I want (1 account number with the corresponding total count of items in the next column).

Example:        Acct#            Count
                        12001            3
                        12005            1
                        12009            2

Can I accomplish the same results with one query, maybe by using a subquery. I don't have access to PL/SQL, therefore it has to be standard query language.

When I run the 2nd query it take quite a bit of time because the querying is done on my desktop. The front end program that I'm using doesn't index.. If I can get rid of the second query my run time would be cut in half.

By the way my initial query is pulling from more than on table.

Any help would be appreciated..

Thanks,
Knez Received on Sat Oct 31 1998 - 17:29:33 CST