Jump to content

SQL Help - Two group bys

Recommended Posts

Update - Nevermind, worked it out :)



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)



staff a,
sites s,
business b

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

order by b.name, s.name
Edited by MiddleAgeCool
Link to post
Share on other sites

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 :)

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...