how to query the sql to get the count-CodePudding

Assume I have the following table

version  is_success(only 1 and 0)
0.1      0
0.1      0
0.1      1
0.1      1
0.2      0
0.2      0
0.2      0

I want to query the following result by sql:

version   count(is_success=0)/count(all based on is_success type)
0.1       2/4=0.5
0.2       3/3=1

CodePudding user response：

You may use aggregation here:

SELECT version, 1.0 * SUM(is_success = 0) / COUNT(*) AS count
FROM yourTable
GROUP BY version;

CodePudding user response：

Actually you want the average value of NOT is_success:

SELECT version,
       AVG(NOT is_success) avg_not_success
FROM tablename
GROUP BY version;

or:

SELECT version,
       AVG(is_success = 0) avg_not_success
FROM tablename
GROUP BY version;

See the demo.

CodePudding user response：

i hope this works fine

select version, count(if(is_success=0, version,null))/count(version) As result from yourTable where is_success>=0 group by version;