mysql - if statement inside a select statement

Question

Name    Value   AnotherColumn
-----------
Pump 1  8000    Something1
Pump 1  1000    Something2
Pump 1  3000    Something3
Pump 2  3043    Something4
Pump 2  4594    Something5
Pump 2  6165    Something6

In this table I want to group by on Name column and give output as name,value_exist.

value_exist will be 1 if 1000 is present in any of the value column for that name group. so the output will be:

Name    value_exist
-----------
Pump 1  1
Pump 2  0

Arion · Accepted Answer · 2015-04-23 09:22:52Z

2

Maybe something like this:

select name, MAX(CASE WHEN value=1000 THEN 1 ELSE 0 END) as value_exist
from your_table
group by name

answered Apr 23, 2015 at 9:22

Arion

31.3k10 gold badges74 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sagar Joon · Accepted Answer · 2015-04-23 09:26:34Z

1

One more :

select name , max(if(value=1000,1,0)) as value_exist
from table 
group by name;

answered Apr 23, 2015 at 9:26

Sagar Joon

1,41715 silver badges23 bronze badges

Comments

juergen d · Accepted Answer · 2015-04-23 09:21:38Z

0

select name, sum(value = 1000) as value_exist
from your_table
group by name

answered Apr 23, 2015 at 9:21

juergen d

205k40 gold badges303 silver badges377 bronze badges

Collectives™ on Stack Overflow

mysql - if statement inside a select statement

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related