May 30, 2021 at 12:08 am
i have a Problem in my code, the case is this. I need to count the amount of employees in all the different branches of a company, lets say its 4 branches. my goal i so print out the branch with the most employees. but the select can not have the count in it, only the name of the branch with the most employees should be in the final print, how can i acheiv this ?
i know i need to aggregate with COUNT, which is easy to use in the select part of the quary.
Any tips ?
I tought about sub queries, making a sub query to get count of alle the branches and then selecting only the branch name from that. but cant get it to work
May 30, 2021 at 5:04 am
I have a Problem in my code
Any tips ?
For starters, how about actually posting the code you're having a problem with?
The "most" bit is confusing. I thought you said you just wanted counts by department or something like that. How about an example? You know, some sample data and expected results?
May 30, 2021 at 5:50 am
You can use a subquery, but you don't technically need it. If branches tie and you want to list all tied branches, change to "TOP (1) WITH TIES".
SELECT TOP (1) Branch
FROM dbo.table_name
GROUP BY Branch
ORDER BY COUNT(Employee_Id) DESC
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 30, 2021 at 10:42 am
Hi! thanks so much for the answer, But in my case, Branch and employee are two different tables. so Employee got the structure
Employee --> EmpID, Name, Phone, BranchID
Branch --> BranchID BranchName.
can you still use count in the orderby with this ?
May 30, 2021 at 5:47 pm
Nvm.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply