Mysql condition for unit of measure specific value

Question

I have a BD view where I need to bring a total amount of KG/LT of products expected to receive in the company, I need to include a condition that when the unit is TON or TN it multiplies by 1000, because when it is ton, it calculates as if it were normal unit, this information is taken from XML, it is possible to?

Follow the current query:

SELECT SUM(i_prod_quant) as QUANT, pha_table.pha_emp as EMP FROM pha_table 
            INNER JOIN phs_table ON pha_table.pha_parceiro_cnpj = phs_table.phs_parceiro_cnpj 
            INNER JOIN nfe_table ON nfe_table.nfe_chave = phs_table.phs_nfe_chave 
            INNER JOIN i_nfe_table ON i_nfe_table.i_nfe_chave = nfe_table.nfe_chave GROUP BY EMP

Answer 1

In your case just make use of a condition with the IF in your select that will work, the IF should see the unit of measure to do the multiplication.

SELECT SUM(IF(UNIDADE='TON', i_prod_quant*1000, i_prod_quant)) as QUANT, pha_table.pha_emp as EMP FROM pha_table 
            INNER JOIN phs_table ON pha_table.pha_parceiro_cnpj = phs_table.phs_parceiro_cnpj 
            INNER JOIN nfe_table ON nfe_table.nfe_chave = phs_table.phs_nfe_chave 
            INNER JOIN i_nfe_table ON i_nfe_table.i_nfe_chave = nfe_table.nfe_chave GROUP BY EMP

You can also have the same effect by using CASE

SELECT SUM(CASE WHEN UNIDADE='TON' THEN i_prod_quant*1000 ELSE i_prod_quant END) as QUANT, pha_table.pha_emp as EMP FROM pha_table 
            INNER JOIN phs_table ON pha_table.pha_parceiro_cnpj = phs_table.phs_parceiro_cnpj 
            INNER JOIN nfe_table ON nfe_table.nfe_chave = phs_table.phs_nfe_chave 
            INNER JOIN i_nfe_table ON i_nfe_table.i_nfe_chave = nfe_table.nfe_chave GROUP BY EMP