CODEWITHSUNDEEP
sqldatabase
Steffan Harris
Steffan Harris

Reputation: 9326

Need help coming up with an sql query for my problem

What I want to do is count the number of employees a publisher employs and then report a list of the names of the publishers along with the total amount of employees. This list is to be ordered by publishers who have the most employees listed first.

Here is a sample of the database. This is actually two databases joined together. So the databases are called publishers, employee.

pub_id  pub_name        city   state/country/emp_id fname minit lname   job_id job_lvl  pub_id hire_date
---------------------------------------------------------------------------------------
0736    New Moon Books  Boston  MA  USA PMA42628M   Paolo   M   Accorti 13  35  0877
0736    New Moon Books  Boston  MA  USA PSA89086M   Pedro   S   Afonso  14  89  1389
0736    New Moon Books  Boston  MA  USA VPA30890F   Victoria    P   Ashworth    6   140 0877
0736    New Moon Books  Boston  MA  USA H-B39728F   Helen       Bennett 12  35  0877
0736    New Moon Books  Boston  MA  USA L-B31947F   Lesley      
0877    Binnet & Hardley    Washington  DC  USA PTC11962M   Philip  T   Cramer  2   215 9952
0877    Binnet & Hardley    Washington  DC  USA A-C71970F   Aria        Cruz    10  87  1389
0877    Binnet & Hardley    Washington  DC  USA AMD15433F   Ann M   Devon   3   200 9952
0877    Binnet & Hardley    Washington  DC  USA ARD36773F   Anabela R   Domingues   8   100 0877

Upvotes: 1

Views: 73

Answers (2)

shaunhusain
shaunhusain

Reputation: 19748

SELECT pub_name, COUNT( emp_id ) AS emp_quan
FROM  `table_name` 
GROUP BY pub_name
ORDER BY emp_quan

Knew that was gonna happen, a second too late :)

Upvotes: 0

Paul Sasik
Paul Sasik

Reputation: 81479

Something like this should work:

SELECT pub_name, COUNT(emp_id) 
FROM your_table_or_view_or_subquery
GROUP BY pub_name
ORDER BY COUNT(emp_id) DESC, pub_name

Upvotes: 5

Related Questions