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

Question

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.

If you're teaching yourself SQL you could pick a more standard DB than MySQL. Each DB has their differences and foibles but MySQL is a stand-out in weird behaviour, especially around grouping. I'd recommend Postgres or one of the free SQL Server versions to start off on the right footing. — LoztInSpace
– LoztInSpace, Commented Oct 28, 2016 at 0:32

Gordon Linoff · Accepted Answer · 2016-10-28 00:30:44Z

7

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.

edited Oct 28, 2016 at 0:30

answered Oct 28, 2016 at 0:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lefty.dev Over a year ago

This helps me out a lot, thanks! And you're right, I am very new and awful at SQL. So I'll look into learning about joins and grouping.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related