CODEWITHSUNDEEP
mysqlsql
Michael Montagnese
Michael Montagnese

Reputation: 50

Incorrect records from SQL query

So im trying to list the department locations, the project names associated with each department and the number of employees that work on each project.

There is a DeptLocations table(Attributes: Dnum, DLoc)

A Project table(Attributes:PName, Pnum, PLoc, DNum)

An Employee table (Attributes: FNAME, M, LNAME, SSN, BDATE, ADDRESS, S, SALARY, SUPERSSN, DNO)

And a Works_On table (Attributes: ESSN, PNO, HOURS).

This is my SQL query:

select DeptLocations.DLocation, Project.PName, count(ESSN) 

from Works_On, DeptLocations, Project, Department 

where DeptLocations.DLocation = Project.PLocation and Project.PNumber = Works_On.PNo

For some reason it only yields 1 record, when clearly there should be plenty more. Any help would be awesome.

Upvotes: 1

Views: 97

Answers (1)

Fahmi
Fahmi

Reputation: 37473

You need to add group by clause - as you've used aggregated function:

select 
  DeptLocations.DLocation, 
  Project.PName, 
  count(ESSN) 
from 
  Works_On 
  inner join Project on Project.PNumber = Works_On.PNo 
  inner join DeptLocations on DeptLocations.DLocation = Project.PLocation 
group by 
  DeptLocations.DLocation, Project.PName

Note: It's better to use explicit join instead of comma separated join.

Upvotes: 1

Related Questions