SQL Help - Two group bys

[Leon 13]

Update - Nevermind, worked it out

 

Hi

Quick SQL question...  I'm sure this is easy to those in the know!

I'm running a quick query against some tables and receiving the following results:
 

 

Business | Site | Staff

Insurance | NCL | Steve

Insurance | NCL | Cath

Insurance | MAN | Bob

Billing | MAN | Sarah

Billing | MAN | Tom

Complaints | MAN | Andy

 

 

I want to aggregate this to have the total number of staff rather than the individuals:

 

Billing | MAN | 2

Complaints | MAN | 1

Insurance | MAN | 1

Insurance | NCL | 2

 

 

I've got so far with the query but can't work out how to group both the sites and the staff.  It only works if I simplify to two columns (Site and staff)

 

select
b.name,
s.name,
count(a.staff_id)

from 
staff a,
sites s,
business b

where 
b.bu_id = s.bu_id
and a.site_id = s.site_id

order by b.name, s.name

Edited February 6, 2017 by MiddleAgeCool

[scoobos 0]

first rule of internet question asking for geeks - if you "work it out" then update your post with the solution....

 

I spent 2 mins reading through it !

[Rayvin 1,506]

I'd also be interested in your solution.

[Ant 346]

i'll reply to this later still travelling so on my phone but you've bigger issues imo

 

why would you have a SiteID in a staff details table? should be nothing in there but their details then you use that id in your other tables to relate where they are contacts and whatever else

 

you should have 4tables 3 with details of staff, business and site, and a lookup table where you have businessid. siteid, staffID 

 

so you can say

StaffID siteid, businessid

10000, 2,  4

10000, 2, 3

 

meaning Jeff with staff id 10000 works in manchester, in 2 businesses complaints and billing

(if jeff can work in multiple departments  then that allows for it or multiple sites etc allows for multiple db controlled role management for segregating duties or permissions etc)

 

likewise then you query one table to do all your count of how many work where in what and if you don't care about the names of people just the numbers ya don't even have to join on the "staff" table, just the other two to get the site/business names

 

i'll do your query when i get in later if i've time

[scoobos 0]

Lol that makes 2 of us who've wasted time