What is wrong with my Subquery?

[WWGD]

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.

 

[Thomas Thelen]

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.

 

[WWGD]

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.

 

[Samy_A]

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.

"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;));

 

[PAllen]

"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;));

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)

 

[Samy_A]

Noting that TOP 1 is proprietary syntax supported by 1/3 of major implementations (others use limit; SQL Standard has a more complex approach)

Oops, correct MySQL doesn't support TOP.

 

[harborsparrow]

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.

 

[pbuk]

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)

 

[WWGD]

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.

Thank you for the offer, I will take you up on it ASAP.