jaxicab jaxicab - 7 months ago 21
SQL Question

SQL Count(Select) Query

I am working on a sql query to do the following:

For each project, retrieve the project number, the project name, the number of employees who work on that project.

Here is what I have so far:

select pno, pname,
count(select fname from

employee inner join works_on
on employee.ssn=works_on.essn

inner join project
on works_on.pno=project.pno)

as num_emp from project


Which gives me this error:


1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select fname from employee inner join works_on on employee.ssn=works_on.essn inn' at line 1


Which I am assuming means I can't put a select statement inside a count function, but I do not see how else to do this

Attached is my schema

ER Diagram

gsd gsd
Answer

I'd do it like this, grouping on the project details for which you want the employee count:

SELECT
    pno,
    pname,
    COUNT(employee.snn) AS num_emp
FROM
    project
INNER JOIN
    works_on
    ON works_on.pno = project.pno
INNER JOIN
    employee
    ON employee.ssn = works_on.essn
GROUP BY
    pno,
    pname;

EDIT:

Actually, if you want to list projects with no employees assigned then you could make your original query more correct by doing something like:

SELECT
    pno,
    pname,
    (
        SELECT
            COUNT(fname)
        FROM
            employee
        INNER JOIN
            works_on
            ON employee.ssn = works_on.essn 
        WHERE
            works_on.pno = project.pno
    ) AS num_emp
FROM
    project;