I have a nested if:
SELECT
aquaculture_id,
qty_prod,
IF(avg_prod = '0', ':', [allows for unknown, data not avail; unobtainable]
IF(qty_prod < (0.5*max_5_prod),'over-exploited', [current catch is < 50 percent of the maximum after a 5-year smoothing]
IF(qty_prod BETWEEN avg_prod_5 AND avg_prod_10, 'fully exploited', [catches stabilized at/close to last 5-10 yrs peak values.]
IF(qty_prod >= avg_prod_10 AND area_change_a > '0', 'non-fully exploited', [catches increased over time when fishing effort has increased]
IF(qty_prod < max__prod_5 AND area_change_a = '0', 'over-exploited_2', ':') [catches have dropped significantly from a peak without a clear cut in fishing effort]
)
)
)
)'bio_a'
FROM fao_aquaculture;
It seeks to identify from the data which species are:
a. non-fully-exploited
b. fully exploited
c. over-exploited
d. over-exploited_2
e. unknown, data not avail; unobtainable
There are two distinct definitions used to define an 'over-exploited' population.
My first run of the query returns A LOT of b.'over-exploited' populations. A smattering of a. non-fully exploited, and d.unknown, data not avail. No d. over_exploited_2 results were returned
I need all four definitions to be considered and for the correct 'answer'to be returned.
Is there any rocket science here in the internal ordering of IF statements that I need to be mindful of? I'm looking to have as much certainty as is reasonable that the result I'm getting reflects the data/truth.
And not that I stuffed up the query.