January 28, 2015 at 10:17 am
Table Dept has two columns (did(int) , dname(varchar)).
Table emp has five columns (eid(int), mid(int), nme(varchar), did(int), city(varchar)).
Now I need to get the names of all departments having more than 1000 employees.
Below is my query to achieve this:
; with cte_nandu as (
select d.dname, count(e.eid) [emp_count] from tb_emp e
inner join tb_dept d
on e.did=d.did
group by d.dname
)
select * from cte_nandu where emp_count > 1000
However, in the above CTE all the count is captured first and only records with count more than 1000 is selected.
Could you optimize the query so that we calculate only the departments with more than 1000 employees?
January 28, 2015 at 10:46 am
I do not know if I understand correctly, but try adding the Having clause:
select d.dname, count(e.eid) [emp_count]
from tb_emp e
inner join tb_dept d
on e.did = d.did
group by d.dname
having count(e.eid) > 1000
Hope this helps.
January 28, 2015 at 10:49 am
toraghubiz (1/28/2015)
Table Dept has two columns (did(int) , dname(varchar)).Table emp has five columns (eid(int), mid(int), nme(varchar), did(int), city(varchar)).
Now I need to get the names of all departments having more than 1000 employees.
Below is my query to achieve this:
; with cte_nandu as (
select d.dname, count(e.eid) [emp_count] from tb_emp e
inner join tb_dept d
on e.did=d.did
group by d.dname
)
select * from cte_nandu where emp_count > 1000
However, in the above CTE all the count is captured first and only records with count more than 1000 is selected.
Could you optimize the query so that we calculate only the departments with more than 1000 employees?
Something like this?
with d1000 as
(select did, Total = count(did) from emp group by did having count(did) > 1000)
select d.*
from d1000
join dept d on d.did = d1000.did
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 28, 2015 at 10:52 am
also is there an index on tb_dept(dname) ? sincethat's the group by, an index would help that specific query.
Lowell
January 28, 2015 at 10:52 am
A quick note: starting a CTE with a semicolon is like starting a sentence with a '.'
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 28, 2015 at 10:58 am
FWIW, imex's query will generate the same plan as yours but it's simpler to write.
Phil's might be better or not, you'll need to test it before you implement it.
For questions about performance, you should read the following article to ensure that you're giving all the information needed. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 28, 2015 at 12:12 pm
toraghubiz (1/28/2015)
...Could you optimize the query so that we calculate only the departments with more than 1000 employees?
That's illogical, captain.
What you could do is precalculate, using insert/delete triggers on the employees table.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply