Number of records that do not satisfy a given filter

Question

Number of records that do not satisfy a given filter

Navigation

#1 by (2 votes)

0

I have this query:

SELECT * FROM Tabela WHERE campo != "";

How do you know the number of records that do not meet this condition?

I know I can create another query and use affected rows to get the 2 values and compare but I wanted to know if there is a faster way.

mysql

asked by anonymous 20.10.2015 / 13:58

1 answer

Confirmation with Bootstrap modal [duplicate] Error using $ this in static function

score 2 · Accepted Answer

I can not tell if MySQL has a native function for this, but as suggested by @rray, you can make a subselect with the total number of records as well as the amount of filtered records and subtract both and get the result you want You wait.

SELECT (c.Total - cc.Retorno) AS Total
FROM (
  (SELECT COUNT(1) AS Total FROM contatos) c, 
  (SELECT COUNT(1) as Retorno FROM contatos WHERE endereco != '') cc
);

Something simpler would reverse your condition:

SELECT COUNT(1) FROM contatos WHERE endereco = '';

You can see the query working in SQL Fiddle