>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