>>I've created an SQL statement for which I'm also trying to get the number of people (without duplicates) who are attending a conference. I initially tried to do this in the report summary (creating a field based on first+middle+last, but that didn't give me the correct results. I then tried the following, figuring that my order header table will contain one record for each person who is attending a conference, so I could just count those (the line below where I use "...as TEMP"), but that didn't work either.
>>
>>The SQL without that "as temp" line creates a cursor which has duplicate names because those people are attending multiple events. I need to know that info, but also need to have just a staight count for my report.
>>
>>

>>
>>SELE NVL(Cfevents.ev_eventdesc,"") AS EVENTS, NVL(Cforderl.ol_quantity,0) AS QTY, ;
>>NVL(Cfpeople.pe_firstna,"") AS FIRST, ;
>>NVL(Cfpeople.pe_middlei,"") AS MIDDLE, NVL(Cfpeople.pe_lastnam,"") AS LAST, ;
>>NVL(Cfjobtyp.jo_jobdesc,"") AS JOB, NVL(Cfdistri.di_name,"") AS DISTRICT, ;
>>COUNT(Cforderh.oh_orderno) AS temp ;
>>FROM Confdata!cfevents ;
>>	LEFT JOIN confdata!cforderl ;
>>	  	ON Cforderl.ol_evcode = Cfevents.ev_code ;
>>	LEFT JOIN confdata!cforderh ;
>>		ON oh_orderno = ol_orderno ;
>>	LEFT JOIN Confdata!Cfpeople ;
>>		on pe_peoplid = oh_peoplid ;
>>    LEFT JOIN confdata!cfjobtyp ;
>>    ON Cfpeople.pe_jobid = Cfjobtyp.jo_jobid ;
>>    LEFT JOIN confdata!cfdistri ;
>>    ON Cfpeople.pe_distcode = Cfdistri.di_code ;
>>   WHERE SUBSTR(Cfevents.ev_code,1,4) = ALLT(STR(YEAR(DATE()))) ;
>>   ORDER BY ev_eventdesc, jo_jobdesc, pe_lastnam ;
>>   INTO CURSOR Rptdata
>>
>><\PRE>
>>
>>Thanks!
>>
>>Sylvia
>
>I dont know if this solves your problem, but you are using COUNT() without a GROUP BY.

Rick -

I don't see how the GROUP BY would help to get the number of unique names in this result - can you explain?  By the way, I subsequently tried putting a DISTINCT in the parentheses ...COUNT(DISTINCT Cfpeople.pe_peopleid) as TEMP and did get a TEMP field with the correct number of unique names.  The problem was, doing that gave me a result set of one record!  Perhaps I'm trying to do something which can't be done.

Sylvia