How to store the value of a MySQL subquery and use it in the WHERE clause of the outter query

Question

I have a MySQL table called 'ad' wich stores a series of ads for a pet ecommerce website. This is the structure of said table:

ad:

ID: ad unique id.

User: id of the user who published the ad

Animal: the type of animal

Race: the race of the animal

Title

Price: the price of the ad

I need to list ads from a specific user that have a price above the average for the same animal and race combination, I've done it with this query and it works perfect:

SELECT * FROM `ad` AS `out_ad`
WHERE `out_ad`.`user` = {iduser} AND
`out_ad`.`price` > (
     SELECT AVG(`price`) FROM `ad` AS `in_ad`
     WHERE `in_ad`.`user` <> `out_ad`.`user`
     AND `in_ad`.`animal` = `out_ad`.`animal`
     AND `in_ad`.`race` = `out_ad`.`race`
)

As I pointed out above, it works fine, but there is one problem, for every row listed in the outer query I would like to have the average price available as another column. For that I give the inner query an alias and place it in the list of columns I want to fetch:

SELECT *, ( SELECT AVG(`price`) FROM `ad` AS `in_ad`WHERE `in_ad`.`user` <> out_ad`.`user`
         AND `in_ad`.`animal` = `out_ad`.`animal`
         AND `in_ad`.`race` = `out_ad`.`race`
         ) AS `avg_precio`
FROM `ad` AS `out_ad`
WHERE `out_ad`.`user` = {iduser}

Then again this works fine, but it doesn't compare wich record is above average, I tried using the subquery alias in the WHERE clause of my outer query, but it gives me this MySQL error: #1054 - Unknown column 'avg_price' in 'where clause'

SELECT *, ( SELECT AVG(`price`) FROM `ad` AS `in_ad`WHERE `in_ad`.`user` <> out_ad`.`user`
         AND `in_ad`.`animal` = `out_ad`.`animal`
         AND `in_ad`.`race` = `out_ad`.`race`
         ) AS `avg_price`
FROM `ad` AS `out_ad`
WHERE `out_ad`.`user` = {iduser} AND `out_ad`.`price` > `out_ad`.`avg_price`

Is there anyway that I can store the inner query to return it as a column and use it in a WHERE statement at the same time?

Thanks for reading!

Answer 1

First of all, from the logic point of view, I would suggest to remove in_ad`.`user` <> `out_ad`.`user condition when calculating AVG(price).

If one user's price is higher than average price (calculated including this user), then of cause her price is higher than average price when calculated without this user. Vice Versa.

After you remove this condition, you can even create a table by:

create table avgprice as
  select animal, race, AVG(price) from ad group by animal, race

then your can can easily join this avgprice table for your query.

Answer 2

You can also wrap the query into an external one and move the problematic condition out there:

SELECT *
FROM
  ( SELECT *, ( SELECT AVG(`precio`) 
                FROM `ad` AS `in_ad`
                WHERE `in_ad`.`user` <> out_ad`.`user`
                  AND `in_ad`.`animal` <> `out_ad`.`animal`
                  AND `in_ad`.`race` <> `out_ad`.`race`
             ) AS `avg_price`
    FROM `ad` AS `out_ad`
    WHERE `out_ad`.`user` = {iduser} 
  ) AS as
WHERE  price > avg_price

Answer 3

I'd do something that looks monstorous but stay with me a little

SELECT
    ad.*,
    ad_avg.price_average
FROM ad
INNER JOIN (
    -- Get the average price per animal
    SELECT
        ad.animal,
        ad.race,
        AVG(ad.price) AS price_average
    FROM ad
    INNER JOIN (
        -- Make sure it only gets ads that the user has selected
        SELECT DISTINCT
            animal,
            race
        FROM ad
        WHERE user = {iduser}
    ) AS ad_usr
    ON ad_usr.animal = ad.animal
    AND ad_usr.family = ad.family
    GROUP BY ad.animal, ad.race
) AS ad_avg
-- Join the averages onto ad
ON ad.animal = ad_avg.animal
AND ad.race = ad_avg.race
AND ad.price > ad_avg.price_average
WHERE ad.user = {iduser}

So, starting at the inside with [QRY1], select the animal/race combinations that the user has ads in so as to limit the ad types that are averaged for performance reasons

        SELECT DISTINCT
            animal,
            race
        FROM ad
        WHERE user = {iduser}

Now a level up, [QRY2], this uses the animals and races from [QRY1] and determines the overall average prices for them.

    SELECT
        ad.animal,
        ad.race,
        AVG(ad.price) AS price_average
    FROM ad
    INNER JOIN (
        [QRY1]
    ) AS ad_usr
    ON ad_usr.animal = ad.animal
    AND ad_usr.family = ad.family
    GROUP BY ad.animal, ad.race

And now back around to the top level query, this joins the above averages table onto the ad table selecting all ads by the desired user, success!

SELECT
    ad.*,
    ad_avg.price_average
FROM ad
INNER JOIN (
    [QRY2]
) AS ad_avg
-- Join the averages onto ad
ON ad.animal = ad_avg.animal
AND ad.race = ad_avg.race
AND ad.price > ad_avg.price_average
WHERE ad.user = {iduser}

And just because I'm in that type of mood

I haven't tried the below query but in theory it should do much the same however requires userid to be specified only once, and joins the innermost [QRY1] select onto a tuple in the [QRY2] WHERE statement. It may be worth giving it a try in case it is more efficient, hard to tell without testing against your dataset

SELECT
    ad.*,
    ad_avg.price_average
FROM ad
INNER JOIN (
    SELECT
        ad.animal,
        ad.race,
        AVG(ad.price) AS price_average
    FROM ad
    WHERE (ad.animal,ad.race) IN (
        SELECT DISTINCT
            ad.animal,
            ad.race
        FROM ad,(SELECT @iduser := {iduser}) v
        WHERE ad.user = @iduser
    )
    GROUP BY ad.animal,ad.race
) AS ad_avg
ON (ad.animal,ad.race) = (ad_avg.animal,ad_avg.race)
AND ad.price > ad_avg.price_average
WHERE ad.user = @iduser