I would like to get mysql to work for me instead of me making php work.
say I have a table that has 2 columns in it. one column is 0-5 and the other is text.
how can I tell mysql to count only the rows that have a value greater than 0 and a value that doesn't equal "", or is NULL
I tried count(row_blah) AS blah, but that counted all of them even though they were zero or NULL. maybe it is my GROUP BY... nope it wasn't that.
any ideas on how I can make a query to do this for me?lets see.... if cola is numeric and colb is not, and you want a count of all cola > 5
select count(
case when a is not null and a!='' and a>0 then 1 else 0 end) as num
)
from database.owner.table
however I don't know if you can use the case statement in mySql. if not, then...
select count(*) as num
from database.owner.table
where a is not null and a!='' and a > 0
if you add colb to the query, you'll just get the same count for a, though different values for b.I don't beleive I can use case, I will check. other than that I didn't think about putting it in the where clause. I was thinking of something else.
I will try that and let you know, thanks docactually I can't use that. I already query the where clause by product_id so I don't want to limit the result as I want all of them that have that product_id. I am just filtering those 2 columns after the query. like showing a total for cola and colb.why not just use a seperate query? I've found in the past that I can make things incredibly difficult for myself my trying to use one query for everything.true and I agree, but this is the second query for the specific product. the first query gets all of them and thsi query get them by the product_id.
so say the first query finds 5 products for this caregory. it wil loop through all the returned rows and spit them out on teh page. now this query I am working on will query teh database again depeing on the product_id of those 5 products to get info from another table depending on if iti s present.
it is kind of like a review system.
return all products in this category, then search the review table for each and every produt. so when the final reuslt is spit ou ton the screen i tthen wil have this info I did in the second query.thanks Doc, you stepped me in the right direction.
select sum(number) as sum_all
, sum(case when text <> ''
then 1
else 0
end ) as numberofnotnulls
, sum(case when number is not null
and rating > '0'
then 1
else 0
end ) as numbergreaterthan0
from table
where id = '{$id}' group by id"
mysql will do the case thing
say I have a table that has 2 columns in it. one column is 0-5 and the other is text.
how can I tell mysql to count only the rows that have a value greater than 0 and a value that doesn't equal "", or is NULL
I tried count(row_blah) AS blah, but that counted all of them even though they were zero or NULL. maybe it is my GROUP BY... nope it wasn't that.
any ideas on how I can make a query to do this for me?lets see.... if cola is numeric and colb is not, and you want a count of all cola > 5
select count(
case when a is not null and a!='' and a>0 then 1 else 0 end) as num
)
from database.owner.table
however I don't know if you can use the case statement in mySql. if not, then...
select count(*) as num
from database.owner.table
where a is not null and a!='' and a > 0
if you add colb to the query, you'll just get the same count for a, though different values for b.I don't beleive I can use case, I will check. other than that I didn't think about putting it in the where clause. I was thinking of something else.
I will try that and let you know, thanks docactually I can't use that. I already query the where clause by product_id so I don't want to limit the result as I want all of them that have that product_id. I am just filtering those 2 columns after the query. like showing a total for cola and colb.why not just use a seperate query? I've found in the past that I can make things incredibly difficult for myself my trying to use one query for everything.true and I agree, but this is the second query for the specific product. the first query gets all of them and thsi query get them by the product_id.
so say the first query finds 5 products for this caregory. it wil loop through all the returned rows and spit them out on teh page. now this query I am working on will query teh database again depeing on the product_id of those 5 products to get info from another table depending on if iti s present.
it is kind of like a review system.
return all products in this category, then search the review table for each and every produt. so when the final reuslt is spit ou ton the screen i tthen wil have this info I did in the second query.thanks Doc, you stepped me in the right direction.
select sum(number) as sum_all
, sum(case when text <> ''
then 1
else 0
end ) as numberofnotnulls
, sum(case when number is not null
and rating > '0'
then 1
else 0
end ) as numbergreaterthan0
from table
where id = '{$id}' group by id"
mysql will do the case thing