Rails Active Record Mysql find query HAVING clause
- by meetraghu28
Is there a way to use the HAVING clause in some other way without using group by.
I am using rails and following is a sample sccenario of the problem that i am facing. In rails you can use the Model.find(:all,:select,conditions,:group) function to get data. In this query i can specify a having clause in the :group param. But what if i dont have a group by clause but want to have a having clause in the result set.
Ex: Lets take a query
select sum(x) as a,b,c from y where "some_conditions" group by b,c;
This query has a sum() aggregation on one of the fields. No if there is nothing to aggregate then my result should be an empty set. But mysql return a NULL row. So this problem can be solved by using
select sum(x) as a,b from y where "some_conditions" group by b having a NOT NULL;
but what happens in case i dont have a group by clause?? a query like below
select sum(x) as a,b from y where "some_conditions";
so how to specify that sum(x) should not be NULL?
Any solution that would return an empty set in this case instead of a NULL row will help and also that solution should be doable in rails.
We can use subqueries to get this condition working with sumthin like this
select * from ((select sum(x) as b FROM y where "some_condition") as subq) where subq.b is not null;
but is there a better way to do this thru sql/rails ??
