CODEWITHSUNDEEP
mysqlsubquery
niu
niu

Reputation: 43

How to use subquery table alias in WHERE clause in MySQL

SELECT invoice_id, sum_amount
FROM (SELECT invoice_id, SUM(amount) AS sum_amount
      FROM invoice
      GROUP BY invoice_id) AS TEMP_TABLE
WHERE sum_amount in (SELECT MAX(sum_amount) 
                     FROM TEMP_TABLE);

When I tried to use the TEMP_TABLE, an error occurred and said TEMP_TABLE doesn't exist. Why doesn't it work? I think the execution order is "FROM" then "WHERE", the table alias has been created at that time.

Upvotes: 1

Views: 1728

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can't calculate max for the previously calculated alias in sub query alternatively you can rewrite your query as 

SELECT a.invoice_id, SUM(a.amount) AS sum_amount,
c.max_amount
FROM invoice AS a
CROSS JOIN (
      SELECT MAX(sum_amount) max_amount FROM(
          SELECT invoice_id, SUM(amount) AS sum_amount
          FROM invoice
          GROUP BY invoice_id
      ) b
) c
GROUP BY a.invoice_id
HAVING sum_amount = c.max_amount

I have created a sample demo for testing purpose

Sample data

INSERT INTO invoice
    (`invoice_id`, `amount`)
VALUES
    (1, 10),
    (2, 5),
    (2, 5),
    (3, 1),
    (4, 2);

In above query you can see maximum sum of amount for invoice is 10 so invoice_id =>1,2 should be returned 1 has amount of 10 and two entries for invoice_id => 2 (5+5 = 10) is also 10 ,3 and 4 should not be included in result set sample output

invoice_id  sum_amount  max_amount
1           10          10
2           10          10

DEMO

Upvotes: 2

Related Questions