CODEWITHSUNDEEP
mysqlsqlxampp
Lefty.dev
Lefty.dev

Reputation: 120

Using avg() function in SQL query returns only one row?

I have this SQL query:

SELECT DISTINCT aname, avg(salary) 
FROM aircraft, certified, employees 
WHERE cruisingrange > 1000 AND ((certified.eid = employees.eid) = (aircraft.aid = certified.aid));

In one column, I am trying to display the aircraft names (aname) of all aircraft with cruising ranges over 1000 miles (cruisingrange > 1000). In the other column, I am trying to display the average salary (avg(salary)) of certified pilots (certified.eid = employees.eid) who are certified to fly that aircraft in particular (entire conditional after AND). However, it's conglomerating all the salaries into a single value and returning that, therefore I am only given a table with two columns and one row instead many rows (it only displays one aircraft name as well).

These SQL queries separately work just fine, though:

SELECT aname 
FROM aircraft 
WHERE cruisingrange > 1000;

SELECT avg(salary)
FROM employees, certified
WHERE employees.eid = certified.eid;

SELECT DISTINCT aname
FROM aircraft, certified
WHERE certified.aid = aircraft.aid;

How do I write a query that does what I'm supposed to do? I just started self-teaching SQL today so sorry if the answer is pretty obvious. Any suggestions are appreciated.

Upvotes: 4

Views: 3209

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270733

You are using MySQL, so use GROUP BY correctly:

SELECT aname, avg(salary) 
FROM aircraft a JOIN
     certified c
     ON a.aid = c.aid JOIN
     employees e
     ON c.eid = e.eid
WHERE cruisingrange > 1000 
GROUP BY aname;

Based on your query, you don't seem to understand SQL very well. So let's start there. Here is some advice:

  • Never use commas in the FROM clause. Always use proper, explicit JOIN syntax.
  • Use meaningful table aliases (abbreviations).
  • Qualify all column names (I can't in the above query, because I don't know where the columns come from).
  • Don't use DISTINCT until you understand SQL. It has big effects on performance and does strange unexpected things, sometimes.

So, what is happening with your query? Basically, consider this related query:

SELECT aname, avg(salary) 
FROM aircraft a JOIN
     certified c
     ON a.aid = c.aid JOIN
     employees e
     ON c.eid = e.eid
WHERE cruisingrange > 1000 ;

How many rows does it return? Well . . . in most databases, it would return 0 rows and an error. This is an aggregation query, but the SQL engine does know what to do with aname.

MySQL allows this syntax, returning one row (because it is an aggregation query with no GROUP BY). The value of aname is taken from an indeterminate row.

Hence, when you add DISTINCT to the SELECT, it has no effect. The query already returns only one row, so it is, by definition, distinct.

Upvotes: 7

Related Questions