Ask Your Question
0

I'm having some trouble with a query, unable to identify the error.

asked 2016-05-09 14:08:41 +0100

spoorthy1997 gravatar image

Can someone help me with this query, it's quite simple but everytime I execute it, it comes back with an error. The question is:

List and total the number of all full-time tutorial staff and their location.

(Tables) Employees (Fields) EmployeeID, FirstName, LastName (Tables) Contracts (Fields) ContractID, EmployeeID, Location, Status

SELECT COUNT(*)"Contracts"."EmployeeID", "Employees"."FirstName", "Employees"."LastName", "Contracts"."Location" 
FROM "Contracts", "Employees" 
WHERE "Contracts"."EmployeeID" = "Employees"."EmployeeID" 
AND "Contracts"."Status" = 'Full-time'
;
edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2016-05-09 15:05:27 +0100

LKeithJordan gravatar image

updated 2016-05-09 20:18:30 +0100

REVISION:

@Ratslinger makes some valid points in his response, and negates the need for the COUNT function in my SQL statement (since the rest of my SQL statement deals with the list). However, I believe a JOIN is still needed for the list of employees. I therefore revise my SQL statement as follows:

SELECT Contracts.EmployeeID, Employees.FirstName, Employees.LastName, Contracts.Location FROM Contracts LEFT JOIN Employees ON Contracts.EmployeeID=Employees.EmployeeID AND Contracts.Status = Full-time;

Also, please be aware I am not testing my script prior to responding (as I prefer to do).

^^^^^^^^^^^^^^^^
ORIGINAL RESPONSE
I'm basing my reply purely on looking at your code snippet, but I would try this:

SELECT COUNT(Contracts.EmployeeID) as NumEmployees, Contracts.EmployeeID, Employees.FirstName, Employees.LastName, Contracts.Location FROM Contracts LEFT JOIN Employees ON Contracts.EmployeeID=Employees.EmployeeID AND Contracts.Status = Full-time;

Please click the check mark next to the response you believe best answers your question.

edit flag offensive delete link more
1

answered 2016-05-09 18:19:16 +0100

Ratslinger gravatar image

updated 2016-05-09 23:19:31 +0100

Your question specifies both a list & a count. A list will return multiple records & a count will return a single record. You also are not clear about the count - my guess here would be a count of the employees who are full-time at each location.

List of Full-time employees:

SELECT "Employees"."EmployeeID", "Employees"."FirstName", "Employees"."LastName", "Contracts"."Location" FROM "Contracts", "Employees"  WHERE "Contracts"."EmployeeID" = "Employees"."EmployeeID" AND "Contracts"."Status" = 'Full-time'

Count of Full-time employees by location:

SELECT "Contracts"."Location",  COUNT("Contracts"."Location") AS "LocationCount" FROM "Contracts" WHERE "Contracts"."Status" = 'Full-time'  GROUP BY  "Contracts"."Location","Contracts"."Status"

When you have a question and the question involves an error, please specify exactly what the error is. Also include LO version & probably OS.

EDIT:

To carry this out to its' fullest I've included a sample Base file: SQLdemo.odb

Contained within are 2 tables (Contracts & Employees) and 3 queries (FullTimeList, CountByLocation, and JoinTables). The first two queries are as stated above. The third query shows how adding a total with detail does not produce clear results (although the total is correct, it is not correct for that record but rather for all 'Full-time' employees in a location). Someone looking at these results would not be able to interpret them correctly!

edit flag offensive delete link more

Comments

@Ratslinger, you make some good points. However, I still believe you are going to need a JOIN for this list of employees.

LKeithJordan gravatar imageLKeithJordan ( 2016-05-09 20:12:13 +0100 )edit

Join is not necessary - both statements tested & work as is. You can, however join the two tables & produce a result from the join showing each employee detail and duplicated COUNT for Contract totals.

Ratslinger gravatar imageRatslinger ( 2016-05-09 20:46:33 +0100 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2016-05-09 14:08:41 +0100

Seen: 43 times

Last updated: May 09 '16