Alternative to the use of view variables in mysql

Question

Alternative to the use of view variables in mysql

Navigation

#1 by (1 votes)

1

I need to create a view that gets the percentage of each product in relation to the total of products, I made the query below and it works as a normal select:

set @total := (SELECT round(sum(gin.Caixa_9L /1000),4) FROM gin where sales_channel = 'duty paid' or sales_channel = 'travel retail');
set @duty_paid := (select round(sum(gin.Caixa_9L /1000),4) from gin where sales_channel = 'travel retail');
select round(((@duty_paid / @total)),1) * 100 as percentual;

It happens that I need to turn this into a view, but mysql does not accept variables in views. How can I proceed? Can you do it any other way?

I was able to solve this:

CREATE DEFINER = 'root'@'localhost'
PROCEDURE gin.'share duty paid'()
BEGIN
set @total := (SELECT round(sum(gin.Caixa_9L /1000),4) FROM gin where
sales_channel = 'duty paid' or sales_channel = 'travel retail');
set @duty_paid := (select round(sum(gin.Caixa_9L /1000),4) from gin where     
sales_channel = 'duty paid');
select round(((@duty_paid / @total)),1) * 100 as percentual;
END

mysql view query

asked by anonymous 10.04.2017 / 15:20

1 answer

Sentences in a Loop in the R How to assign an image to a ListView Item?

score 1 · Accepted Answer

You do not need to use variables, you can do this:

SELECT 
round(((aux2.duty_paid / aux1.total)),1) * 100 as percentual
FROM (

    (SELECT 
                round(sum(gin.Caixa_9L /1000),4) AS total
        FROM gin 
        WHERE sales_channel = 'duty paid' 
            or sales_channel = 'travel retail') AS aux1,
    (SELECT 
                round(sum(gin.Caixa_9L /1000),4) AS duty_paid 
     FROM gin 
     WHERE sales_channel = 'duty paid')  AS aux2

)