Thanks Cetin

I used the crosstab query wizard before I got your reply. But I will bear it in mind.

Nick



>>Hi
>>
>>I want to summarise sales by month for a given period for different salespeople so I sum(total) group by yearmonth salesperson etc.
>>
>>Fine but I also want the months when there are no sales. Whats the best method in sql of getting that .
>>
>>Anyone ?
>>
>>Thanks
>>
>>Nick
>
>Nick,
>To do that first create a dummy cursor that contains all the months to join on. ie:
>
>

Select emp_id, Month(order_date) As salesMonth, Count(*) As sales ;
>  FROM (_samples+'data\orders') ;
>  WHERE Year(order_date) = 1994 ;
>  GROUP By 1,2

Have missing emp_id, month combinations. First generate a emp_id, monthId for all employees and months and join on it:
>
>

>Create Cursor months (monthId i)
>For ix=1 To 12
>  Insert Into months Values (m.ix)
>Endfor
>
>Select fullSet.emp_id, monthId As salesMonth, sales ;
>  FROM ;
>  (Select emp_id,monthId From (_samples+'data\employee'), months) fullSet ;
>  LEFT Join  ;
>  (Select emp_id, Month(order_date) As salesMonth, Count(*) As sales ;
>  FROM (_samples+'data\orders') ;
>  WHERE Year(order_date) = 1994 ;
>  GROUP By 1,2) ord On fullSet.emp_id = ord.emp_id And fullSet.monthId = ord.salesMonth ;
>  ORDER By 1,2
>

>
>PS: Query is VFP9 style using subqueries instead of separate SQL. In versions where this is not supported you can instead do separate queries.
>
>Cetin