Alternative to using view variables in mysql

Asked

Viewed 309 times

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 this working 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;

I need to transform this into a view, but mysql does not accept variables in views. How can I proceed? da para fazer de outra maneira?

I managed to sort it out like 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 query view
  • 1

    Create a function and then call it from the view. Or you can create a computed column in your table.

  • 1

    @I managed to create a precedent, and inside it I put my query with the variables. Still thanks for the help.

  • 1

    If possible, put your solution code in the answer, it may serve other people in the future. Contribute to the community ;)

1 answer

1


It is not necessary to use variables, you can do this way:

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

)

Browser other questions tagged mysql query view

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