I wants to write a query to retrieve COUNT(of employees with the salary=1000) and COUNT(of total no of employees) from the same table.
any ideas??
views:
82answers:
4
+3
A:
SELECT COUNT(EmployeeID) as 'Total Employees',
(SELECT COUNT(EmployeeID) FROM Employees WHERE Salary = 1000) as 'Salaried'
FROM Employees
Nick
2010-07-08 19:27:50
Would you query the same table twice?
Raj More
2010-07-08 19:41:59
the use of a subquery in the select list is less efficient than single query like [@Tom H. answer](http://stackoverflow.com/questions/3207157/problem-with-count/3207303#3207303). Using `SET SHOWPLAN_ALL ON` this query TotalSubtreeCost almost 50% higher than the single query of @Tom H. answer
KM
2010-07-08 19:51:24
A:
select count(*) as employeeCount,
(select count(*) from employee where salary=1000) as bigmoneyEmployeeCount
from employee
kekekela
2010-07-08 19:29:53
+5
A:
Another method:
SELECT
COUNT(*) AS total_employees,
SUM(CASE WHEN salary = 1000 THEN 1 ELSE 0 END) AS employees_with_1000_salary
FROM
Employees
Tom H.
2010-07-08 19:40:47
+2
A:
select
count(*) totalCount,
count(case when salary = 1000 then 1 else NULL end) specialCount
from Employees
COUNT counts non-null rows.
this produces the same execution plan as the [answer from @Tom H.](http://stackoverflow.com/questions/3207157/problem-with-count/3207303#3207303). Also, you really don't need the `else NULL`, but removing it does not alter the execution plan.
KM
2010-07-08 19:57:25
@KM: Just putting it here as most of the people don't seem to know what COUNT actually counts:) Especially that count(*) equivalent to count(1), but not count(column) if column is nullable ...
2010-07-08 20:01:57
+1, @ToxicAvenger, I agree, most don't understand COUNT. your answer is just as fast as the [answer from @Tom H.](http://stackoverflow.com/questions/3207157/problem-with-count/3207303#3207303) and much better than the ones with a subquery in the select list!
KM
2010-07-08 20:12:37