Monday, March 26, 2012

help with simple query using group by and where clause

have a table with sale_id, date, sales_person_id

i need to find out the sales_person_id's who did 1 sales every month
from jan 2003 and another query who did a sales every quarter.

How many sales person have atleast one sale every month (excluding prints) for either 2003, 2004, or 2005?

How many sales person had atleast 25 sales each year 2003-2005

It is not clear how you have the date specified in the table. And also what does excluding prints mean? Is there some other column that tracks this detail? If so, you need to include the condition in the query below:

SELECT COUNT(*) as NumSalesPerson

FROM (

SELECT year(s.date) as yr, s.sales_person_id

FROM Sales AS s

WHERE s.date >= '20030101' and s.date <'20060101'

GROUP BY year(s.date)

HAVING COUNT(*) >= @.n -- can be 1 or 25 or whatever

) AS s1

No comments:

Post a Comment