Subquery Using IN mysql Error Code: 1054

Question

I'm essentially trying to rewrite a query so that SUM(amount) is calculated as a subquery, with the database named "sakila":

SELECT first_name, last_name, SUM(amount) AS totalSpent
FROM sakila.customer c JOIN
     sakila.payment p
     ON c.customer_id = p.customer_id
GROUP BY last_name, first_name;

What I have is:

SELECT first_name, last_name, SUM(amount) AS totalSpent FROM sakila.customer c WHERE c.customer_id IN (SELECT customer_id FROM sakila.payment p) GROUP BY last_name, first_name;

However, when I try to run it, it says ERROR CODE 1054, Unknown column 'amount' in 'field list'

EER Diagram

You don't have column amount in table customer. Check your table structure. — Hotdin Gurning
– Hotdin Gurning, Commented Oct 9, 2017 at 2:24

Tim Biegeleisen · Accepted Answer · 2017-10-09 02:22:34Z

Here is your current query, which I actually think is the way to go here:

SELECT
    first_name,
    last_name,
    SUM(amount) AS totalSpent
FROM sakila.customer c
INNER JOIN sakila.payment p
    ON c.customer_id = p.customer_id
GROUP BY
    first_name,
    last_name;

This is a fairly lean query because you are doing a join with a simple aggregation. If you wanted to compute the amount as a subquery, you could do so via a correlated subquery on the payment table:

SELECT
    first_name,
    last_name,
    SELECT(SUM(amount) FROM sakila.payment p
           WHERE p.customer_id = c.customer_id) AS totalSpent
FROM sakila.customer c;

Note that this query has a much highest cost because the subquery in the SELECT statement is correlated to the outer query. This means that MySQL would have to run a separate query for each row of the customer table. Your first query is probably the way to go here, because it would allow for things like an index and other optimizations.

Zymon Castaneda · Accepted Answer · 2017-10-09 02:23:03Z

0

First of all, you encountered the amount error because you didn't specify which source it comes from so I checked what is your expected result and came up with this:

SELECT c.first_name, c.last_name, SUM(p.amount) AS totalSpent
FROM sakila.customer c
    INNER JOIN payment p ON p.customer_id = c.customer_id
GROUP BY c.last_name, c.first_name;

answered Oct 9, 2017 at 2:23

Zymon Castaneda

75912 silver badges23 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2017-10-09 02:25:42Z

0

An alternative is to use a correlated subquery:

SELECT c.first_name, c.last_name,
       (SELECT SUM(p.amount)
        FROM sakila.payment p
        WHERE p.customer_id = c.customer_id
       ) AS totalSpent
FROM sakila.customer c;

This saves the aggregation in the outer query. And it can take advantage of an index on payment(customer_id, amount) for performance.

Note that your query doesn't work because you can only refer to columns in tables (or views or subqueries) referenced in the FROM clause.

answered Oct 9, 2017 at 2:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Victoria Over a year ago

Thanks so much! This makes a lot more sense!

Collectives™ on Stack Overflow

Subquery Using IN mysql Error Code: 1054

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related