avoid NULL count mysql

Question

I have this select:

select 'fv' prefix, c.user, MAX(f.data) as data, c.user, count(c.user) as logs
from favorites f inner join
     cadastro c
     ON f.user = c.id
where f.fv = '1' and c.user <> f.user and f.data > now()

If I have no result to show, mysql shows this:

prefix - user - data - user - logs
fv NULL NULL NULL 0

I don't want to show when there is no results. What is wrong? is the count(c.user)? how can I avoid count to count 0?

Gordon Linoff · Accepted Answer · 2015-10-13 12:47:21Z

2

An aggregation query with no group by always returns one row. One simple method is to add an aggregation:

select 'fv' as prefix, c.user, MAX(f.data) as data, c.user, count(c.user) as logs
from favorites f inner join
     cadastro c
     ON f.user = c.id
where f.fv = '1' and c.user <> f.user and f.data > now()
group by f.fv;

If there are no matches, this will not return any rows.

answered Oct 13, 2015 at 12:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

avoid NULL count mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related