How to execute two querys in a statement?

Asked

Viewed 632 times

3

I need to mount a Mysql query that returns the total of rows, and the total of lines with value greater than 1 in the same query.

Total of lines:

SELECT COUNT(*) FROM tabela

Total of lines with a value greater than 1: 

SELECT COUNT(*) FROM `tabela` WHERE `valor` < 1

It would be like joining these two lines into one, and returning me the total of lines and the total of lines with value greater than 1.

php mysql sql
  • 1

    And why do you have this requirement?

  • Why don’t I want to run two lines of code, and I can run it in one only

  • 1

    And what gain will you have like this?

  • We’re missing the point of the question

  • 3

    On the contrary, you are trying to get away from the focus of the question. Answering this is relevant.

  • Excuse me, it is not to be rude, however somewhere in the OS says that questions cannot be based on opinions etc etc.. But if you insist so much, I personally prefer to run on one line everything I need, why go twice to the bakery being that I can go only one and save my feet?

  • user3163662 I believe he is speculating a better way to formulate a good answer for you, is not necessarily a matter of opinion, but his last sentence makes sense (at least for me), maybe if the title was something like "How to count the number of lines in two different querys", but still it’s a good question.

  • It would be good to give a review, at that time stayed several s/ accept.

Show 3 more comments

4 answers

4

The two selections will have to be made separately but can bring the result at once by combining the two selections into a third. The select can return any information, even if it does not depend on the database. In this case you will receive a result where the "field" total shall represent the first consultation and totalMaior 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"];

I put in the Github for future reference.

3

Would be simple.

Try:

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

2

Has a simple form:

SELECT COUNT(*) as total_linhas,
       IF(valor < 1, COUNT(*), null) as total_maior_que_um
 FROM tabela

2

You can do it this way too:

select a.id,  b.id from (SELECT COUNT(*) as id from TABELA) a, (SELECT COUNT(*) as id from TABELA WHERE valor > 1) b

Browser other questions tagged php mysql sql

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