SQL: Select and courting distinct rows

Posted on 2008-11-11
Last Modified: 2012-05-05
I have a table "Staff" with columns: id, salary, and branchNo. I need to write a query to count the number of staff members in each distinct branch and find the sum of the salaries in each branch. Unless I write multiple select statements hardcoding the unique branchNo's I can't think of a clearer, more dynamic approach.
Question by:troubledFish
    LVL 142

    Accepted Solution

    this should do:
    select branchNo, count(*), sum(salary)
      from yourtable
    group  by branchNo

    Open in new window

    LVL 26

    Expert Comment


    SELECT branchNo, COUNT(*), SUM(salary)
    FROM Staff
    GROUP BY branchNo
    LVL 17

    Expert Comment


    SELECT DISTINCT a.BranchNo, (SELECT Count(*) FROM Staff WHERE BranchNo = a.BranchNo) StaffCount, 
    (SELECT Sum(Salary) FROM Staff WHERE BranchNo = a.BranchNo) SumSalary
    FROM Staff a

    Open in new window

    LVL 17

    Expert Comment

    Yeah, you can do it the EASY way like angelIII and tigin44 suggest. Or you can do it the HARD way, the HoggZIlla way. LOL. Sorry. Yes, definately use above with group by. Time to go home. LOL

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now