Jump to content

SQL Help - Two group bys


Recommended Posts

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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...