Printing the Highest count

Question

Post reply

Printing the Highest count

beefycarrot

SSC Enthusiast

Points: 115
More actions
May 30, 2021 at 12:08 am

#3889745

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

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?

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

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".

beefycarrot SSC Enthusiast Points: 115 More actions · Answer 3

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 ?

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

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".