troubledFish
asked on
SQL: Select and courting distinct rows
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
SELECT branchNo, COUNT(*), SUM(salary)
FROM Staff
GROUP BY branchNo