September 15, 2009 at 7:41 am
I'm using a Group By statement to count items, such as the statement below. It works, the only problem is if the count is 0 for a department, it doesn't show up at all. How can I get it to display
the department and a '0' for a department that exists in the table, but returns 0 results for the
condition?
SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;
September 15, 2009 at 7:46 am
you have to join against the master table of all the departments;
otherwise if it doesn't exist in the employees table you cannot display it
something like this:
select
alldepartments.department ,
COUNT(employees.employeeid) as "Number of employees"
from alldepartments
left outer join employees on alldepartments.department= employees.department
WHERE salary > 25000
GROUP BY alldepartments.department;
Lowell
September 15, 2009 at 7:49 am
you have to remove the criteria from the query. You can place it in a case when statement and use sum instead of count
SELECT department, sum(case when salary > 25000 then 1 else 0 end) as "Number of employees"
FROM employee
GROUP BY department;
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 15, 2009 at 7:52 am
tnocella (9/15/2009)
I'm using a Group By statement to count items, such as the statement below. It works, the only problem is if the count is 0 for a department, it doesn't show up at all. How can I get it to displaythe department and a '0' for a department that exists in the table, but returns 0 results for the
condition?
SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;
Lowell's version is more likely the correct way to do this. Please examine his first. Another way to get this information out of just that table should be:
SELECT DISTINCT Department, EC
FROM employees E
LEFT JOIN (SELECT Department, COUNT(*) EC
FROM employees
WHERE salary > 25000
GROUP BY Department
) S
ON E.Department = S.Department
That is assuming you mean you want to show departments even if they don't have any employees making over 25000, and you want your count to be of employees who DO make over 25000.
[Edit] Mike's is better as well (Wasn't there when I started posting)
September 15, 2009 at 7:52 am
Or...
SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY ALL department;
September 15, 2009 at 7:55 am
All better. No more replies from me until I've had some coffee! Great reminder about ALL keyword Howard.
September 15, 2009 at 8:09 am
And here's the reminder about that keyword from SQL Server 2008 BOL:
ALL
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
I'd use Mike's query.
--Jonathan
September 15, 2009 at 8:13 am
Jonathan (9/15/2009)
ALLThis feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
What a killjoy! Here I was all excited about maybe applying ALL somewhere sometime soon. Ah well. Thanks for the heads up Jon 🙂
September 15, 2009 at 8:15 am
That's news to me as well. Good spot.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply