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);
  • 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 '17 at 16:49
  • Yes, that is correct. Sorry if I wasn't too clear. – Brendan May 26 '17 at 17:57
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, privacy policy and cookie policy

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