Can I put a WHERE for each column?

Asked

Viewed 126 times

-1

I’m willing to put a condition for each column in my SELECT, this is possible?

SELECT count(id_casa) as casaAzul, count(id_casa) as casaAmarela FROM tb_casa

Where: Where column 1: Where cor_casa = 'blue' Where column 2: Where cor_casa = 'yellow'

mysql sql database

4 answers

2

You are counting twice the same column (id_casa) and calling one from casaAzul and another from casaAmarela. What you should do is group the houses by color and give a Count on it.

Mysql moved very little, I can’t remember what the command would look like.. Below follows in Oracle for you to base (should be very similar, do tests and google the possible changes).

SELECT COR_CASA as COR, COUNT(*) as QUANTIDADE
  FROM TB_CASA
 WHERE COR_CASA IN ('azul', 'amarela')
 GROUP BY COR_CASA

  • So, what if I want to count the houses that have the number greater than 500 and less than 500 ? I use the in(n_casa > 500, n_casa < 500) ?

  • Yes, you can. But do you want to put the two clauses in the same select? I ask because if you put < 500 and > 500, all the numbers will come except the 500. Then it would be simpler if you put a clause just saying n_casa other than 500 (in Oracle is the symbol different is <>, in Mysql should be this or !=). Now, if you want to do it separately, just include a clause AND N_CASA > 500 , and then make another select with AND N_CASA < 500

  • In fact, this was just one example, the real problem is that: I have a list of trucks, where I sort whether they are light or heavy from a calculation. I want to separate the quantity of the weighed and the quantity of the other grouped by date. RESULT: | Date | Heavy | Other | | 10/04/2014 | 121 | 45 | | 11/04/2014 | 355 | 121 | . . .

  • Only one addendum: The "in" command functions as an OR. In this example you could put: Where COR_CASA in ('blue', 'yellow') or Where COR_CASA = 'blue' OR COR_CASA = 'yellow'. They are both equivalent. That your filter n_casa > 500 goes with an AND clause because you want the houses to be blue/yellow And the number to be more than 500.

  • I understand your doubt.. in this case you will have to group by date and by truck type. Is this truck date and type information in a single table? What is the table name and the name of these columns?

  • They are in a table, the table is called tb_vbv, I define whether they are weighed or not by a column called Gross, where they are weighed if Gross > 12600 and others if Gross < 12600

  • I get it.. what’s the name of the date column you need to show ?

  • The column name is date

  • Let’s go continue this discussion in chat.

Show 4 more comments

2


One option to do this count is:

SELECT
    SUM(CASE cor_casa = 'azul' THEN 1 ELSE 0 END) AS casaAzul,
    SUM(CASE cor_casa = 'amarela' THEN 1 ELSE 0 END) AS casaAmarela
FROM tb_casa;

1

We managed to solve!

Follow the SQL code:

SELECT DATE(DATE) AS DATA, 
(SELECT COUNT(*) 
FROM TB_VBV T 
WHERE DATE(T.DATE) = DATE(T1.DATE) 
AND GROSS > 12600 ) as QUANTIDADE_PESADOS, 
(SELECT COUNT(*) 
FROM TB_VBV T 
WHERE DATE(T.DATE) = DATE(T1.DATE) 
AND GROSS < 12600 ) as QUANTIDADE_OUTROS 
FROM TB_VBV as T1
GROUP BY DATA 
ORDER BY DATA

0

Yes, just use the logical SQL operators such as AND, for example:

SELECT nm_casa, count(id_casa) FROM tb_casa
WHERE nm_casa = 'minhacondição' AND id_casa = 'minhacondição'

More about logical sql operators.

  • And if he needs you to be OR?

  • I got confused on the question, but I didn’t mean it, I’ll edit it.

Browser other questions tagged mysql sql database

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