CODEWITHSUNDEEP
mysql
Alphy
Alphy

Reputation: 331

MySQL group by based on column having a given set of data

I want to produce an accurate dataset from mysql based on true and false logic, if I have say 4 rows and a column has status, if in the status any row has a zero the data group should be regarded as false else true

e.g
k   |  s
---------    //This if i group by k, it returns 1 while i 
a     0      //want it to return 0 on group
a     0
a     1
a     1

k   |  s
---------   //This one if i group by k will return 1 which is very true
a     1
a     1
a     1
a     1

Suggestions

Upvotes: 0

Views: 53

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

Try this query:

SELECT k,
       CASE WHEN SUM(s) < COUNT(s) THEN 0 ELSE 1 END AS label
FROM yourTable
GROUP BY k

This would work well assuming that the only values in the s column are 0 or 1. The logic here is that if even a single row has a zero value, then the SUM(s) for a given k group would be less than the number of records in that group.

Upvotes: 1

Related Questions