I have some SQL code, and when I run it, I get an error:
SELECT AVG (DISTINCT E.salary) AS avrage
FROM aircraft AS A, certified AS C, employees AS E
WHERE A.cruisingrange > 1000
AND A.aid = C.aid
AND C.eid = E.eid
Error:
How can I fix this error?
MS Access does not directly support DISTINCT syntax inside of an aggregate function. One workaround here would be to take the average of a subquery which finds the distinct salaries:
SELECT AVG(salary) AS avrage
FROM
(
SELECT DISTINCT E.salary
FROM (aircraft AS A
INNER JOIN certified AS C ON A.aid = C.aid)
INNER JOIN employees AS E ON C.eid = E.eid
WHERE A.cruisingrange > 1000
) t;
Note that I have also converted your old school implicit join syntax to modern explicit join syntax. This is the preferred way of writing SQL these days.
countwhere your current query is withavg.JOINsyntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouragedAVG(DISTINCT)is basically never the right solution to a problem. What are you trying to calculate?