CODEWITHSUNDEEP
mysqlsql
Jochen Ritzel
Jochen Ritzel

Reputation: 107648

Select different data based on the result of count

I have a very simply query like this:

 SELECT users.id, COUNT(others.id) as c
 FROM users JOIN other ON users.id=others.user_id 
 GROUP BY users.id

What I would like to do is to replace the number in c with '' when c=0.

For example, instead of a row like

 # I have this
 1, 0
 2, 1

 # I want c=0 replaced with ''
 1, ''  
 2, 1

How can I do this in MySQL?

Upvotes: 0

Views: 101

Answers (3)

Mark Willis
Mark Willis

Reputation: 1741

You could try this:

SELECT users.id, IF(COUNT(others.id) > 0, COUNT(others.id), "''") as c
FROM users JOIN other ON users.id=others.user_id 
GROUP BY users.id

Alternatively there is COALESCE but if may not work with the 0 result

"''" (2 single quotes inside 2 double qoutes) will give you

1, '' instead of

1,

Upvotes: 0

Mariusz Jamro
Mariusz Jamro

Reputation: 31653

Use CASE or IF

SELECT t.id, CASE t.c = 0 THEN '' ELSE t.c FROM (
    SELECT users.id as id, COUNT(others.id) as c
    FROM users JOIN other ON users.id=others.user_id 
    GROUP BY users.id
 ) as t

Upvotes: 3

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Try this:

SELECT users.id, IF(COUNT(others.id) = 0, '', COUNT(others.id)) as c
FROM users JOIN other ON users.id=others.user_id 
GROUP BY users.id

Upvotes: 3

Related Questions