Number of records that do not meet a given filter

Asked

Viewed 56 times

0

I have this query:

SELECT * FROM Tabela WHERE campo != "";

How to know the amount of records that do not meet this condition?

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

mysql

  • Do a Count of the entire table, then do a Count with that condition and subtract the two results.

1 answer

2


I can’t tell if Mysql has a native function for this, but as suggested by @rray, you can subselect with the total amount of records and also with the amount of filtered records and subtract both and get the result you expect.

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 be to reverse your condition:

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

You can see the query working on SQL Fiddle

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.