I need to mount a MySQL query that returns me the total of rows, and the total of rows with a value greater than 1 in the same query.
Total lines :
SELECT COUNT(*) FROM tabela
Total lines value greater than 1:
SELECT COUNT(*) FROM 'tabela' WHERE 'valor' < 1
It would be like putting these two lines together, and returning the total number of rows and total rows with a value greater than 1.
The two selections will have to be made separately but you can bring the result in one go by combining the two selections in a third. The'select pode retornar qualquer informação, mesmo que não dependa do banco de dados. Neste caso você receberá um resultado onde o "campo" total representará a primeira consulta e total% 'will represent the second.
SELECT (SELECT COUNT(*) FROM 'tabela') AS total,
(SELECT COUNT(*) FROM 'tabela' WHERE 'valor' > 1) AS totalMaior;
In PHP I would use something like this:
$resultado = mysqli_query($conexao, "SELECT (SELECT COUNT(*) FROM 'tabela') AS total,
(SELECT COUNT(*) FROM 'tabela' WHERE 'valor' > 1) AS totalMaior;");
$campos = mysqli_fetch_array($resultado, MYSQLI_ASSOC);
echo $campos["total"];
echo $campos["totalMaior"];
It would be simple.
Try it out:
SELECT
( SELECT COUNT(*)
FROM tabela
) AS TR,
( SELECT COUNT(*)
FROM tabela WHERE valor < 1
) AS TRCVM1
This will return 2 TR results and TRCVM1.
TR = SELECT COUNT(*) FROM tabela
TRCVM1 = SELECT COUNT(*) FROM tabela WHERE valor < 1
You can do this too:
select a.id, b.id from (SELECT COUNT(*) as id from TABELA) a, (SELECT COUNT(*) as id from TABELA WHERE valor > 1) b
It has a simple way:
SELECT COUNT(*) as total_linhas,
IF(valor < 1, COUNT(*), null) as total_maior_que_um
FROM tabela