• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

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.
0
troubledFish
Asked:
troubledFish
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now