My Sql, query with two different parameters for the same column to bring two different results

Asked

Viewed 30 times

0

I have a situation that I need to calculate the total number of rooms over 50 meters, then I need to calculate those over 100 meters, and so on. Is there a way I can do it in the same query? I need the result by city:

Como tem que aparecer o resultado

calculate one at a time is very quiet:

select
    CIDADE,
    COUNT(SALA) as totalSalaAte50
from
    EDIFICACOES
where
    AREA_SALA <= 50
GROUP by
    CIDADE;
mysql query

  • yes it is possible but you have to put the table structure in question, and better if you have some data. Ah even better if you can build an example on http://sqlfiddle.com/ ai it is possible to help you :)

1 answer

1

Yes it is possible to do this. Note that each count must have a criterion, which is "ROOM < 50", etc.

To do this, you can user inside the COUNT one CASE WHEN to validate the footage for each criterion you need, for example:

COUNT (CASE WHEN SALA < 50 THEN 1 END) AS ATE50

This means that, "if the room is less than 50, returns 1", that is, account 1.

Then just follow the same reasoning for others:

SELECT CIDADE,
       COUNT (CASE WHEN SALA < 50 THEN 1 END) AS ATE50,
       COUNT (CASE WHEN SALA BETWEEN 50 AND 99 THEN 1 END) AS ATE100,
       COUNT (CASE WHEN SALA BETWEEN 100 AND 149 THEN 1 END) AS ATE150
  FROM Edificacoes
 GROUP BY CIDADE;

You can see it working here: http://sqlfiddle.com/#! 9/737b24/9

Browser other questions tagged mysql query

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