Mysql: count as alias with where or having

Question

Im struggling with two apparently easy queries.

Query n1 (WORKS):

SELECT
  cliente.*,
  COUNT(*)                                           AS 'num_installazioni',
  CONCAT(provincia.nome, ' (', provincia.sigla, ')') AS 'provincia'
FROM q_cliente AS cliente
  JOIN dettaglio_installazione AS dettinst ON cliente.id = dettinst.cliente
  JOIN q_sede AS sede ON sede.id_cliente = cliente.id
  JOIN quadra_provincia_table AS provincia ON provincia.id = sede.id_provincia
WHERE 1 = 1 AND 'num_installazioni' < 5
GROUP BY cliente.id;

Query n2 (DOESN'T WORK):

SELECT
  cliente.*,
  CONCAT(provincia.nome, ' (', provincia.sigla, ')') AS 'provincia',
  COUNT(*)                                       AS 'num_pulizie'
FROM q_cliente AS cliente
  JOIN q_sede AS sede ON sede.id_cliente = cliente.id
  JOIN quadra_provincia_table AS provincia ON provincia.id = sede.id_provincia
  JOIN quadra_attivita AS att ON cliente.id = att.id_cliente
WHERE 1 = 1 AND 'num_pulizie' >= 5 AND 'num_pulizie' <= 10
GROUP BY cliente.id;

Query n2 (WORKING VERSION):

SELECT
  cliente.*,
  COUNT(*)                                           AS `pulizie`,
  CONCAT(provincia.nome, ' (', provincia.sigla, ')') AS 'provincia'
FROM q_cliente AS cliente
  JOIN q_sede AS sede ON sede.id_cliente = cliente.id
  JOIN quadra_provincia_table AS provincia ON provincia.id = sede.id_provincia
  JOIN quadra_attivita AS att ON cliente.id = att.id_cliente
GROUP BY cliente.id
HAVING `pulizie` >= 5 AND `pulizie` <= 10;

Context:

I can't really figure out why the first one works, according to some other answers i've found here on stackoverflow:

I should have used COUNT(*) alias in HAVING clause according to those answers, and in fact it works (see 3rd query) but the fun fact is that query n1 is VERY similar and yet it works. What I'm sure it changes is the number of rows of the joining table to which I'm counting over (one has over 4k rows (query n2), the other one has less then 100 (query n1).

In addition to that query n2 (working version) uses ` while query n1 (working) uses '. I'm so confused...

Can someone please explain me what I'm doing (or thinking) wrong?

score 0 · Accepted Answer · answered Jun 16 '17 at 10:19

0

WHERE 1 = 1 AND 'num_installazioni' < 5

You are not using an alias here - you are checking if the text literal num_installazioni is “less than” 5.

https://dev.mysql.com/doc/refman/5.7/en/identifiers.html

It “works” as in it does not throw an error, because you did not use an alias in the WHERE clause here (which you can’t.) If you think this gives you a result that makes any sense however, you are completely in the wrong here.

answered Jun 16 '17 at 10:19

CBroe

91,630
14
92
150

I guess u're right, i had that in mind aswell but i was getting distracted by the fact that the first one actually returns some result, i had to verify them manually to understand that something was wrong (db doesn't contain data i know something about). I'll mark this as accepted, thank you – Diego Jun 16 '17 at 10:52

Mysql: count as alias with where or having

1 Answers1