Select emp_id, Month(order_date) As salesMonth, Count(*) As sales ; > FROM (_samples+'data\orders') ; > WHERE Year(order_date) = 1994 ; > GROUP By 1,2Have 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 >>