Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

What is wrong with my Subquery?

  1. Dec 18, 2015 #1

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    My apologies in advance: I have tried to install MSSQL2014 many times unsuccessfully (I get a bunch of .zip files, but no executable) so I cannot test my queries. I looked for places to " outsource" my queries, I was told of MySql fiddle , but I can't see how to run my database there. I am just going over a class video and trying to do things on my own and then check against the answer given. If someone knows of a public source for setting up databases and running queries, I would appreciate any ref.

    Anyway, I have a table Employee containing , among others, EmpId and ReportsTo. I am trying to do a query to find out which employee has the most other employees reporting to them. I am using this subquery:

    Select employeeID, Count(ReportsTo) from Employee
    Group by employeeId having
    Count(ReportsTo) >= (select max(Count(ReportsTo)) from employee group by EmpId)

    The answer given basically swaps employeeID and ReportsTo.

    I am trying to understand what I did wrong. Thanks for any comments.
     
  2. jcsd
  3. Dec 19, 2015 #2
    Are you installing it using the installer? I'm pretty sure MYSQL also runs as a service. The only executable you want to be manually starting is the workbench. From there you can start the server(service) and have a graphical view of the database.
    If you can't get the installer to work then try installing a full stack package like WAMP, LAMP, XAMPP, or MAMP.

    Executing queries is a trivial task; It can be done in most modern languages using MYSQL Connector.
    Here is how to do it in python.
     
  4. Dec 19, 2015 #3

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Thank you, Thomas, do you mean I can execute queries in MySQL, including executing a query to fill my database, the one I want to query? I had no trouble installing MySQL, it is MSSQL2014 workbench I had trouble with. The two main mainstream news (really entertainment) channels, FOX and MSNBC sell radical politics, Right and Left respectively. It sells, so it continues. I see little real debate outside of that. Mixing news with business does not seem like a good idea.
     
    Last edited: Dec 19, 2015
  5. Dec 19, 2015 #4

    Samy_A

    User Avatar
    Science Advisor
    Homework Helper

    "Select employeeID, Count(ReportsTo) from Employee Group by employeeId " will give you one record per employee. You actually want one record per reportsTo.
    To count the number of employees that report to each employee you have to group by reportsTo:
    "Select reportsTo, Count(Employee) from Employee Group by reportsTo"

    If you want only the employee(s) who has the most employees reporting to him you can use a subquery in the having clause:
    SELECT reportsTo, Count(employeeID) FROM Employee GROUP BY reportsTo
    HAVING (Count(employeeID) In
    (SELECT top 1 Count(employeeID) FROM Employee GROUP BY reportsTo ORDER BY Count(employeeID) DESC;));
     
    Last edited: Dec 19, 2015
  6. Dec 21, 2015 #5

    PAllen

    User Avatar
    Science Advisor
    Gold Member

    Noting that TOP 1 is proprietary syntax supported by 1/3 of major implementations (others use limit; SQL Standard has a more complex approach), you can even more simply express Samy_A's approach without this feature at all (the following would be supported on any plausible SQL implementation):

    SELECT reportsTo, Count(employeeID) FROM Employee GROUP BY reportsTo
    HAVING Count(employeeID) = (select max(cr) from
    (SELECT Count(employeeID) as cr FROM Employee GROUP BY reportsTo ) v1)

    You can also express this without using GROUP BY or HAVING, though there is no particular reason to avoid these:

    select employeeID, (select count(*) from employee e where e.reportsto = eo.employeeID) cr
    from employee eo
    where (select count(*) from employee e where e.reportsto = eo.employeeID) =
    (select max(cr) from (select (select count(*) from employee e where e.reportsto=eo.employeeID) cr from employee eo) v1)
     
    Last edited: Dec 21, 2015
  7. Dec 22, 2015 #6

    Samy_A

    User Avatar
    Science Advisor
    Homework Helper

    Oops, correct MySQL doesn't support TOP.
     
  8. Dec 22, 2015 #7

    harborsparrow

    User Avatar
    Gold Member

    I am interested to know more about your failures to install SQL Server 2014; I have installed it multiple times on multiple machines with very few issues. If you would like to message me about that, please do. If you have a healthy Windows desktop of any recent version and plenty of disk space and RAM, this should work just fine. And then you would have access to all the free Microsoft SQL tools.
     
  9. Dec 22, 2015 #8
    IMHO the easiest way to a working RDMBS setup on Windows is:
    1. Install XAMPP
    2. Forget about the MySQL console, use phpMyAdmin (follow the link from the localhost home page, or click MySQL -> Admin in the XAMPP control panel)
     
  10. Dec 22, 2015 #9

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    Thank you for the offer, I will take you up on it ASAP.
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook