CODEWITHSUNDEEP
mysqlsql
Victoria
Victoria

Reputation: 15

Subquery Using IN mysql Error Code: 1054

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

Upvotes: 1

Views: 532

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

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.

Upvotes: 0

Zymon Castaneda
Zymon Castaneda

Reputation: 759

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;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

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.

Upvotes: 5

Related Questions