How to use "ALIAS" in "WHERE" in MYSQL

Question

I'm not getting to use "ALIAS" in the "WHERE" MYSQL, in code below I need to do something like:

AND ( custo_percapita_regiao + comissao_representante ) < consig.valor_pago_comissao_representante

========================== code ===========================

SELECT

    `consig`.`id`,
    `consig`.`referencia_prod`,
    `consig`.`valor_liquido`,
    `consig`.`percentual_comissao_representante`,
    `consig`.`valor_pago_comissao_representante`,

    `consult`.`id` AS id_consultor,
    `consult`.`nome` nome_consultor,

    `repres`.`id` AS id_representante,
    `repres`.`nome` nome_representante,

    (
        ( SELECT ( `custo_alimentacao` + `custo_gasolina` + `custo_hospedagem` + `outros_custos` ) 
          FROM `regioes` 
          WHERE `id` = `consult`.`regiao` 
        ) / 
        ( SELECT COUNT(*)
          FROM `consignacoes`
          INNER JOIN `consultores` ON `consultores`.`id` = `consignacoes`.`consultor`
          WHERE `consultores`.`regiao` = `consult`.`regiao`
          AND `consignacoes`.`excluido` = "N"
          AND `consignacoes`.`valor_primeiro_pagamento` > 0
        )
    ) AS custo_percapita_regiao,

    (`consig`.`valor_liquido` * `consig`.`percentual_comissao_representante` / 100 ) AS comissao_representante


FROM `consignacoes` `consig`

INNER JOIN `consultores` `consult` ON `consult`.`id` = `consig`.`consultor`
INNER JOIN `administradores` `repres` ON `repres`.`id` = `consig`.`representante`

WHERE `representante` = 3

AND `consig`.`excluido` = "N"
AND `consig`.`valor_primeiro_pagamento` > 0

ORDER BY `consult`.`regiao` ASC, `consult`.`nome`

Put that query in a subquery, and filter in the outer one: `SELECT .... FROM ( put your query here ) AS sub WHERE custo_percapita_regiao ...` — Mahmoud Gamal, Jul 08 '13 at 12:55

Gimmy · Answer 1 · 2013-07-08T16:07:58.273

Try using variables:

SET @custo_percapita_regiao = 0;
SET @comissao_representante = 0;
SELECT

    `consig`.`id`,
    `consig`.`referencia_prod`,
    `consig`.`valor_liquido`,
    `consig`.`percentual_comissao_representante`,
    `consig`.`valor_pago_comissao_representante`,

    `consult`.`id` AS id_consultor,
    `consult`.`nome` nome_consultor,

    `repres`.`id` AS id_representante,
    `repres`.`nome` nome_representante,
    @custo_percapita_regiao :=
    (
        ( SELECT ( `custo_alimentacao` + `custo_gasolina` + `custo_hospedagem` + `outros_custos` ) 
          FROM `regioes` 
          WHERE `id` = `consult`.`regiao` 
        ) / 
        ( SELECT COUNT(*)
          FROM `consignacoes`
          INNER JOIN `consultores` ON `consultores`.`id` = `consignacoes`.`consultor`
          WHERE `consultores`.`regiao` = `consult`.`regiao`
          AND `consignacoes`.`excluido` = "N"
          AND `consignacoes`.`valor_primeiro_pagamento` > 0
        )
    ) AS custo_percapita_regiao,

    @comissao_representante := (`consig`.`valor_liquido` * `consig`.`percentual_comissao_representante` / 100 ) AS comissao_representante


FROM `consignacoes` `consig`

INNER JOIN `consultores` `consult` ON `consult`.`id` = `consig`.`consultor`
INNER JOIN `administradores` `repres` ON `repres`.`id` = `consig`.`representante`

WHERE `representante` = 3 AND (@custo_percapita_regiao + @comissao_representante ) < consig.valor_pago_comissao_representante

AND `consig`.`excluido` = "N"
AND `consig`.`valor_primeiro_pagamento` > 0

ORDER BY `consult`.`regiao` ASC, `consult`.`nome`

Strangely this solves the problem, however generates another: When I run the query, it returns the results, however if I run the same query again, it returns an empty set. — Paulo Reis, Jul 08 '13 at 15:20
It is using a user variable. Think these have session scope. You can probably initialise them in the SQL by adding _CROSS JOIN (SELECT @ custo_percapita_regiao :=0 , @ comissao_representante := 0) sub1_ just before the WHERE clause if this is the select you would like to use (remove the spaces I have had to put after the @, as it is treating the variables as users to be informed of this comment!) — Kickstart, Jul 08 '13 at 15:38
You can set the variables to 0 each time, like in the modified answer, or if you store this query in a procedure, the variables reinitialize each time. About procedure variables: http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference — Gimmy, Jul 08 '13 at 16:10

score 0 · Accepted Answer · answered Jul 08 '13 at 13:27

Possibly like this:-

SELECT
    `consig`.`id`,
    `consig`.`referencia_prod`,
    `consig`.`valor_liquido`,
    `consig`.`percentual_comissao_representante`,
    `consig`.`valor_pago_comissao_representante`,
    `consult`.`id` AS id_consultor,
    `consult`.`nome` nome_consultor,
    `repres`.`id` AS id_representante,
    `repres`.`nome` nome_representante,
    Sub1.RegionTot / Sub2.RegionCount AS custo_percapita_regiao,
    (`consig`.`valor_liquido` * `consig`.`percentual_comissao_representante` / 100 ) AS comissao_representante
FROM `consignacoes` `consig`
INNER JOIN `consultores` `consult` ON `consult`.`id` = `consig`.`consultor`
INNER JOIN `administradores` `repres` ON `repres`.`id` = `consig`.`representante`
LEFT OUTER JOIN 
(
    SELECT `id`, ( `custo_alimentacao` + `custo_gasolina` + `custo_hospedagem` + `outros_custos` ) AS RegionTot
    FROM `regioes` 
) Sub1 ON Sub1.id = `consult`.`regiao` 
LEFT OUTER JOIN 
(
    SELECT `consultores`.`regiao`, COUNT(*) AS RegionCount
    FROM `consignacoes`
    INNER JOIN `consultores` ON `consultores`.`id` = `consignacoes`.`consultor`
    WHERE `consignacoes`.`excluido` = "N"
    AND `consignacoes`.`valor_primeiro_pagamento` > 0
    GROUP BY `consultores`.`regiao`
) Sub2 ON Sub2.`regiao` = `consult`.`regiao` 
WHERE `representante` = 3
AND `consig`.`excluido` = "N"
AND `consig`.`valor_primeiro_pagamento` > 0
AND (Sub1.RegionTot / Sub2.RegionCount) < (`consig`.`valor_liquido` * `consig`.`percentual_comissao_representante` / 100 )
ORDER BY `consult`.`regiao` ASC, `consult`.`nome`

(partly swapping the sub selects from correlated ones in the SELECT to joins so the values can easily be used)

How to use "ALIAS" in "WHERE" in MYSQL

2 Answers2