CODEWITHSUNDEEP
mysqlsql
arm5077
arm5077

Reputation: 302

Using subquery's alias elsewhere in the SELECT table

I have a database of prices that doctors charge for medical procedures within a region. I'd like to compare each doctor's price (average_submitted_charge_amount) to the average price using a subquery.

SELECT *, 
    (SELECT Avg(average_submitted_chrg_amt) AS average 
    FROM    medicare 
    ) AS peerAverage, 
( ( average_submitted_chrg_amt - peerAverage.average ) / peeraverage.average 
) AS charge_deviation 
FROM medicare

MySQL throws an error: Unknown column 'peerAverage' in 'field list'. Is referencing a subquery by alias elsewhere within the query not allowed?

Upvotes: 0

Views: 85

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

Move the calculation to the from clause:

SELECT m.*, peerAverage
      ((average_submitted_chrg_amt - peerAverage) / peeraverage
      ) AS charge_deviation 
FROM medicare m cross join
     (SELECT Avg(average_submitted_chrg_amt)  AS peerAverage 
      FROM medicare 
     ) const;

Upvotes: 1

Lucas Henrique
Lucas Henrique

Reputation: 1364

There is a parentheses ")" more:

Try:

SELECT *, 
    (SELECT Avg(average_submitted_chrg_amt) AS average 
    FROM    medicare 
    ) AS peerAverage, 
( ( average_submitted_chrg_amt - peerAverage.average ) / peeraverage.average 
) AS charge_deviation 
FROM medicare

To help you more, post the tables.

Upvotes: 0

Related Questions