Re: What is wrong with this query?

sergiugp_at_aol.com (Sergiu gp) wrote in message news:<20020212060201.10819.00001049_at_mb-ba.aol.com>...
> Guys,
>
> I wonder why am I getting a not a single-group group function error with this
> query:
>
> SELECT distinct owner_id ||' ' as "Owner ID", Count(distinct owner_id) as
> "Purch. Count", Sum(price) as "Purch. Count",
> avg(price) as "Avg. Purch."
> FROM purchase_tbl
> WHERE purchase_date BETWEEN
> TO_DATE('01-01-1998', 'mm-dd-yyyy') and
> TO_DATE('12-31-2000', 'mm-dd-yyyy')
>
> Do I have to use a nested query, and if yes, which column should I use for the
> inner query?

You don't have a group by function, and count, sum and avg either need to be used alone (so without extra columns) or with group by. It is unclear what you are exactly trying to accomplish. It looks like you try to group by owner_id and at the same time want to know how many different owners you have (count distinct owner_id). Or do you want to know how many purchases they made. In that case you need to use count(*), remove the distinct from the first column and add group by under the where.

Hth

Sybrand Bakker,
Senior Oracle DBA Received on Tue Feb 12 2002 - 15:54:25 CET