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.
troubledFishAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this should do:
select branchNo, count(*), sum(salary)
  from yourtable
group  by branchNo

Open in new window

0
 
tigin44Commented:

SELECT branchNo, COUNT(*), SUM(salary)
FROM Staff
GROUP BY branchNo
0
 
HoggZillaCommented:

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

0
 
HoggZillaCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.