CODEWITHSUNDEEP
mysqlsqlconditional-statements
PRGRAMMR42
PRGRAMMR42

Reputation: 11

Aggregate rows if one row meets a specific condition

I'm trying to select the sum of the values in the isOK column for each Name separated, BUT only if isOK = 1 on Day = 2.

The query for the following example table tablename

Name | Day | isOK  
char | int | int   
-----------------
Flo  |   1 |    1  
Seb  |   1 |    1  
Tim  |   1 |    0  
Flo  |   2 |    1  
Seb  |   2 |    0  
Tim  |   2 |    1

should give Flo: 2 and Tim: 1, but not Seb: 1, since his isOK on Day = 2 is 0.

I've tried using SUM(isOK) with IF constructs, but it's just not working. My alternative solution, to select all Name where isOK = 1 first and select the SUM(isOK) for each of the names is slow and seems in need of improvement.

I guess it's not that difficult, but I've been trying for hours now and I just can't combine my two queries into one.

Upvotes: 0

Views: 332

Answers (3)

Strawberry
Strawberry

Reputation: 33945

SELECT x.name, SUM(y.isOK) total
  FROM my_table x
  JOIN my_table y
    ON y.name = x.name
 WHERE x.day = 2
   AND x.isok=1
 GROUP
    BY x.name;

Upvotes: 0

jpw
jpw

Reputation: 44891

One way to do this is to use a conditional expression together with a having clause like this:

select name, sum(isOk) ok_sum
from your_table
group by name
having sum(case when day = 2 and isOK = 1 then 1 else 0 end) > 0;

With your sample data the result would be:

name    ok_sum
Flo     2
Tim     1

As MySQL evaluates boolean expressions as 1 or 0 it should be possible to reduce the condition to this:

having sum(day = 2 and isOK = 1) > 0;

Another way to do it would be to use a correlated subquery that makes sure there exists a row with Day = 2 and isOk = 1 for the Name:

select t1.name, sum(t1.isOk) ok_sum
from your_table t1
where exists (
    select 1 
    from your_table t2
    where t2.day = 2 and t2.isOK = 1 and t1.name = t2.name
) 
group by t1.name

Upvotes: 1

Satender K
Satender K

Reputation: 581

TRY this :

SELECT 
    name, SUM(isok) AS isOk
FROM
    table
GROUP BY `name`
HAVING SUM(`day` = 2 AND isok = 1) > 0;

Upvotes: 0

Related Questions