Re: Help- SQL*PLUS Reports

<analyst_user_at_my-dejanews.com> wrote in message news:7f0feu$pur$1_at_nnrp1.dejanews.com...
> SELECT TO_CHAR(DETAIL.S_DATE,'MM/DD/YY') S_DATE,
> EMP.LNAME||','||EMP.FNAME PERSON,
> COUNT(DECODE(DETAIL.CAR_TYPE,'N',0)) N,
> COUNT(DECODE(DETAIL.CAR_TYPE,'T',0)) T,
> COUNT(DECODE(DETAIL.CAR_TYPE,'P',0)) P,
> COUNT(DETAIL.CAR_TYPE) TOTAL
> FROM DETAIL,EMP
> WHERE EMP.EMP_CD=DETAIL.EMP_NUM
> GROUP BY EMP_NUM
> ORDER BY 2;
>
> The problem with that SELECT is that I cannot use the first two items,
S_DATE
> and the LNAME, FNAME because of the GROUP BY CLAUSE.

If you're sure that CAR_TYPE, S_DATE, LNAME, FNAME fields are unique for each EMP_NUM
(I think you are) you may use aggregate function (like this: TO_CHAR(max(DETAIL.S_DATE),'MM/DD/YY'))
If not - you have to include all this fields into CROUP BY clause.

Bye, Yaroslav Received on Thu Apr 15 1999 - 09:50:07 CEST