Re: Query Optimization Help needed

Try:

select acct#, count(distinct Item) from item group by acct%;

this should perform better than a subquery.

Kirk

knez wrote:

> 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 Thu Dec 17 1998 - 13:17:41 CST