This error occurs in this SQLite query:
select f.rid, c.title, sum(some_expression) as ratio
from doesntmatter
where c.active = 1 or (ratio = 1.0 and c.active = 0
and c.deactivated in (1, 2, 3, 4, 5)
group by f.rid
This question solves it with having clause:
select f.rid, c.title, sum(some_expression) as ratio
from doesntmatter
where c.active = 1 or (c.active = 0
and c.deactivated in (1, 2, 3, 4, 5)
group by f.rid
having ratio = 1.0
But in my case this changes the semantics of the expression. Do you know how to achieve the expression that is in the original where clause?
Basically, you can't reference an aliased column in a where clause. Take a look at this question to see how to workaround that.
Aditionally, you should add to the group by all non-aggregated columns present in the select statement, in this case, c.id and c.title.
It is difficult to rewrite that query for you because the logic is a bit difficult to understand (because of the wrong group by).
Edit:
After a second thought, you might just need to correct the group by.
select c.id, c.title, sum(some_expression) as ratio
from doesntmatter
where c.active = 1 or (c.active = 0 and c.deactivated in (1, 2, 3, 4, 5))
group by c.id, c.title
having ratio = 1.0
Or maybe:
select f.rid, sum(some_expression) as ratio
from doesntmatter
where c.active = 1 or (c.active = 0 and c.deactivated in (1, 2, 3, 4, 5))
group by f.rid
having ratio = 1.0
I can't help you with the previous part because I don't know the fields nor the data of your tables nor how to interpret them. But selecting fields not present in the group by is a bad idea. Anyway, remember you can always join back to the original tables and get the info you are looking for, not just the fields displayed in the select.