Re: SQL Help

Paul,

You need 'GROUP BY' in your sub query:

select CAM_ID,count(*) cnt from CAM_DAY group by CAM_ID

Oracle has to know how you want it to count. Your GROUP BY must include all columns in the select statement that are not referenced by a group function.

Good luck!
Greg

Paul Davies wrote in message <3731cd0f_at_newsread3.dircon.co.uk>...
>I have the following query:
>
> BEGIN
> select
> sum(
> (M_N + M_B + (K_N* cd) + (C_B * cd))
> * (7 / cnt)
> )
> into
> sold
>
> from A_REQ o, CAM c,(select CAM_ID,count(*) cnt from
>CAM_DAY) CAM_DAY
> where
> c.CAM_ID = CAM_DAY.CAM_ID (+)
> and .........
>
>*
>When I run the query I get this error:
>
>ERROR at line 1:
>ORA-00937: not a single-group group function
>ORA-06512: at "SYSTEM.GETSOLD", line 25
>ORA-06512: at line 1
>
>caused by the subquery (select CAM_ID, count(*) cnt from CAM_DAY)
>
>I've also seen this error when I use other aggregate functions like sum().
>I'm new to Oracle and would appreciated it if someone could enlighten me as
>to what it means and how I can stop it.
>
>Thanks
>
>Paul
>
>
>
>
Received on Thu May 06 1999 - 13:21:39 CDT