Printing the Highest count

  • 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

  • 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?

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

  • 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 ?

  • 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