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