How to do operators in multiple select statements? MySQL-CodePudding

The goal is Forested land / (forested non-forested land area)

here is what I have tried, but shows errors such as

operand should contain 1 column(s)

below I just tried to add up first. Need your help

SELECT DISTINCT Year, State, Total_Forested

(SELECT Distinct Year, State, Hectares AS Total_Forested  FROM forest_greenproject.`forested and non-forested areas, malaysia, 2000 - 2017`  WHERE Category = 'Non-Forested')
 
(SELECT Distinct Year, State, Hectares AS Total_Forested   FROM forest_greenproject.`forested and non-forested areas, malaysia, 2000 - 2017`   WHERE Category = 'Forested') 

FROM forest_greenproject.`forested and non-forested areas, malaysia, 2000 - 2017`

CodePudding user response：

You can apply conditional aggregation, as @Akina already pointed out in the comments section.

That is, you're using an aggregation (the SUM aggregation function) but only on those values which satisfy a condition, in this case Category = 'Forested'. Then you can divide this value by the sum of all "hectares" values (assuming that you can have only two choices in the "Category" field - either forested or not).

When you apply an aggregation (like the SUM), don't forget to add a GROUP BY clause that should contain every non-aggregated field in the SELECT clause, namely "Year" and "State".

SELECT Year, 
       State, 
       SUM(CASE WHEN Category = 'Forested' THEN Hectares END) / SUM(Hectares) 
FROM forest_greenproject.`forested and non-forested areas, malaysia, 2000 - 2017`
GROUP BY Year,
         State