Count records only if larger than the current datetime, with Count()

Question

I have the following query:

SELECT u.nome, u.email, u.login, u.data_cadastro, COUNT(*) AS qtd
FROM patrocinadores AS p
   LEFT JOIN anuncios AS a ON a.id_user = p.id_user
   INNER JOIN usuarios AS u ON u.id = p.id_user
WHERE p.id_patrocinador = '1' GROUP BY a.id_user

She pulls all the records that were sponsored by ID 1. And it also returns me how many ads each sponsored registration has.

This command works perfectly, only I need to pull only the amount of active ads, that is, only when the column expira > time().

When I leave the query thus, with the WHERE a.expira > '...',

SELECT u.nome, u.email, u.login, u.data_cadastro, COUNT(*) AS qtd
FROM patrocinadores AS p
   LEFT JOIN anuncios AS a ON a.id_user = p.id_user
   INNER JOIN usuarios AS u ON u.id = p.id_user
WHERE p.id_patrocinador = '1' AND a.expira > '1449365367' GROUP BY a.id_user

she only returns to me the records in which a.expira is longer than the current time. I want it to return all, but if the column time is less than the current time then it counts as 0 that record.

I just want to make a system that counts how many advertisements the accounts are from the sponsor 1 has assets.

Answer 1

I would suggest using this:

SUM( IF( a.expira > 1449365367, 1, 0 ) )

Explanation:

The SUM serves to add values of a field, and based on this we create a special "field" that will give us 1 for all cases we want to count, and zero for those who do not.

The function IF falls like a glove in this case as it will return 1 if a.expira > 1449365367, else, zero. The syntax of the IF function is this:

IF( condição, resultado se positivo, resultado se negativo)