5

I'm not too familiar with MySQL variables but I'm trying to avoid redundant sub selects while calculating these results. The first execution only returns 1 of the correct calculations. The second execution returns all the correct values.

SELECT
@discount_code := '*******' AS discount_code,
@sales_revenue := (
    SELECT
        CAST(SUM(total_price) AS UNSIGNED)
    FROM
        shopify_orders
    WHERE
        discount_code = @discount_code
) AS sales_revenue,
@rsp_float := (
    SELECT
        revenue_share_percentage / 100
    FROM
        kpi_data
    WHERE
        CODE = @discount_code
) AS rsp_float,
@marketing_spend := (
    SELECT
        fixed_media_cost + @revenue_share
    FROM
        kpi_data
    WHERE
        CODE = @discount_code
) AS marketing_spend,
@roi_dollars := (
    @sales_revenue - @marketing_spend
) AS roi_dollars,
@revenue_share := (@sales_revenue * @rsp_float) AS revenue_share,
@roi := CAST(
    (@roi_dollars /@marketing_spend) AS DECIMAL (5, 2)
) AS roi,
@mroi := CAST(
    (
        @sales_revenue / @marketing_spend
    ) AS DECIMAL (5, 2)
) AS mroi;

First execution: First execution

Second execution: Second execution

2
  • 4
    The order of columns is important, if you are going to use mysql variables this way. You are using @revenue_share before it is calculated. – ypercubeᵀᴹ Dec 14 '15 at 20:22
  • @ypercube thanks so much. I can't believe I missed that. – Daniel Smith Dec 14 '15 at 20:53
6

The order of columns is important, if you are going to use mysql variables this way. You are using @revenue_share before it is calculated. So, the first time the query runs, NULL is used for the calculation of @marketing_spend. The second time the query runs, the variable has a value saved from the 1st run.

Additionally, the two subqueries that have FROM kpi_data can be combined in one. And you don't really need to use variables. You can get the same results using derived tables and one parameter only (@discount_code), used three times:

SELECT
    @discount_code AS discount_code,
    sales_revenue,
    rsp_float,
    marketing_spend
    sales_revenue - marketing_spend AS roi_dollars,
    revenue_share,
    CAST( ((sales_revenue - marketing_spend) / marketing_spend) AS DECIMAL (5, 2) )
        AS roi,
    CAST( (sales_revenue / marketing_spend) AS DECIMAL (5, 2) )
        AS mroi
FROM
    (   SELECT
            sr.sales_revenue,
            kd.revenue_share_percentage / 100  AS rsp_float,
            kd.fixed_media_cost,
            sr.sales_revenue * kd.rsp_float AS revenue_share,
            kd.fixed_media_cost + (sr.sales_revenue * kd.rsp_float) 
                AS marketing_spend 
        FROM
            (   SELECT
                    CAST(SUM(total_price) AS UNSIGNED) AS sales_revenue
                FROM
                    shopify_orders
                WHERE
                    discount_code = @discount_code 
            ) AS sr
          CROSS JOIN
            kpi_data AS kd
        WHERE
            kd.CODE = @discount_code
    ) AS t ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.