Leon 13 Posted February 6, 2017 Report Share Posted February 6, 2017 (edited) Update - Nevermind, worked it out HiQuick 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 Link to post Share on other sites
scoobos 0 Posted February 8, 2017 Report Share Posted February 8, 2017 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 ! Link to post Share on other sites
Rayvin 1,333 Posted February 8, 2017 Report Share Posted February 8, 2017 I'd also be interested in your solution. Link to post Share on other sites
Ant 332 Posted February 8, 2017 Report Share Posted February 8, 2017 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
scoobos 0 Posted February 10, 2017 Report Share Posted February 10, 2017 Lol that makes 2 of us who've wasted time Link to post Share on other sites
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now