469,315 Members | 1,553 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

SQL Query

Table structure:
Region: region_id, name
Employee: employee_id, name, region_id
Sales: sales_id, employee_id, sale_date, sale_amount -there’s an individual entry in the sales table for each sale

Need query for the following scenario:
Query that returns a list of the Regions and the number of employees in each region. The employee only gets added to the count if they had total sales greater than $50,000 last month. Sort the information by the number of employees per region who fit this criteria
Jul 11 '07 #1
1 974
Vidhura
99
Table structure:
Region: region_id, name
Employee: employee_id, name, region_id
Sales: sales_id, employee_id, sale_date, sale_amount -there’s an individual entry in the sales table for each sale

Need query for the following scenario:
Query that returns a list of the Regions and the number of employees in each region. The employee only gets added to the count if they had total sales greater than $50,000 last month. Sort the information by the number of employees per region who fit this criteria
Try the following

select R.Name,count(E.employee_id) from Region R
inner join tEmployee E on E.region_id=R.region_id
inner join (Select employee_id ,sum(sale_amount) as sale_amount from Sales where datepart(m,sale_date)=datepart(m,getdate())-1 group by employee_id) as S
on S.employee_id=E.employee_id
Where S.sale_amount >50000
group by R.Name
order by count(E.employee_id)
Jul 11 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by jaysonsch | last post: by
9 posts views Thread by netpurpose | last post: by
3 posts views Thread by Harvey | last post: by
4 posts views Thread by Diamondback | last post: by
14 posts views Thread by Dave Thomas | last post: by
4 posts views Thread by Stan | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.