1

Using two tables, I am trying to show the years where the average price from Table1(Green_Certs) is greater than the sum of the lowest two prices from Table2(Trading). The problem is I don't know how to Sum the two lowest values from the second table. This is my code so far. Any help is appreciated. Thanks

Select Year_Traded
From Green_Certs
Where Avg(Price)>
(Select min(Price)
     From Trading);
2
  • Would this be a correct re-statement of your request? For every year on record, I want to check if the average price from Green_Certs for that year's records is greater than the sum of the lowest two values from Trading for that year's records. If so, @Rick James answer should still work - however, you would have to limit the rows in Trading based on Green_Certs.Year_Traded - using information we don't know about that would be in Trading.
    – RDFozz
    May 26, 2017 at 16:49
  • Yes, that is correct. Sorry if I wasn't too clear.
    – Brendan
    May 26, 2017 at 17:57

2 Answers 2

1
SELECT Price
    FROM Trading
    ORDER BY Price
    LIMIT 2;

will find the 2 lowest values.

Wrap that to get the SUM (or AVG or whatever):

SELECT SUM(Price) as sum_price
    FROM ( SELECT Price
            FROM Trading
            ORDER BY Price
            LIMIT 2 )

Then (I think) use that instead of the subquery you have.

0

you hvn't explained fully.

Per year Avg price should be greater than sum of 2 lowest price per year

or

Per year Avg price should be greater than sum of 2 lowest price

Try something like this

select years,AVgPrice FROM
(
select years, avg(price) AVgPrice
from table
group by years
)t4
where AVgPrice>
(
select sum(price) Price
from
(
select top 2 price
 from table2
 order by price
 )
 )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.