Try
count(DIST a.oeno) as oecount, ;
>Hello everybody.
>
>if have a 'flexible' querry for a report which so far has worked great. however, i now need to include the option 'Sales Order Count' and 'Sales Item Count'. This is giving me great trouble.
>
>As i have mentioned, this querry is put together mostly with variables as there are many option the user can choose from to create this report. I have simplified the sql in the following example to be able to explain and show the problem i have.
>
>in this sql i always have 2 tables: comast (sales order header) and codet (sales order detail). they are connected by the field 'oeno'.
>
>here is an example i have tried:
>sele ;
> sum(b.usell*b.qtyord) as sales, ;
> count(a.oeno) as oecount, ;
> month(a.reqdate) as nmonth ;
> from comast a, codet b ;
> where ;
> a.oeno = b.oeno .and. ;
> "100" $ a.custclass .and. ;
> between(a.reqdate, {^2003/01/01}, {^2003/12/31}) ;
> group by nmonth
>this querry gives me a cursor with a total on dollars ordered, grouped by each month. I want to also see how many sales orders there are for each month. The above test however (count(a.oeno)), totals the order line items in codet.
>
>i am not sure if there is a way of doing this and what i am missing here. please help.
>
>Thanks for any help in advance!
--sb--