Calculated MySQL column using variable not returning right result

Question

I'm trying to use defined variables to calculate a column but the result does not add up correctly. I've tried moving the variable declaration inside the brackets or playing around with the aggregation but the calculated column (Profit in the example below) does not match the result of just writing out the full calculation.

Please have a look at example query below. Query contains a full join so I included it since it might be relevant?

select
    a.ID,
    c.Group1,
    a.group2,
    b.group3,
    @turnover :=
        sum( 
            a.Qty * 
            a.Price * 
            (100 - a.discount) / 100 *
            a.Rate
        ) as Turnover,
    @cost :=
        sum(
            a.Qty *
            a.Cost
        ) as Cost,
    @turnover - @cost as Profit
from
    a,
    b,
    c 
where
    a.id=b.id and
    a.id2=c.id
group by
    a.ID,
    c.Group1,
    a.group2,
    b.group3

Expected outcome:
╔════╦════════╦═════════╦═════════╦══════════╦═══════╦════════╗
║ ID ║ Group1 ║ Group 2 ║ Group 3 ║ Turnover ║ Cost  ║ Profit ║
╠════╬════════╬═════════╬═════════╬══════════╬═══════╬════════╣
║  1 ║ A      ║ X       ║      E  ║ 2.13     ║ 1.25  ║ 0.89   ║
║  2 ║ A      ║ Y       ║      F  ║ 1.93     ║ 1.61  ║ 0.32   ║
║  2 ║ A      ║ X       ║      G  ║ 46.74    ║ 36.93 ║ 9.81   ║
╚════╩════════╩═════════╩═════════╩══════════╩═══════╩════════╝
Actual outcome:
╔════╦════════╦═════════╦═════════╦══════════╦═══════╦════════╗
║ ID ║ Group1 ║ Group 2 ║ Group 3 ║ Turnover ║ Cost  ║ Profit ║
╠════╬════════╬═════════╬═════════╬══════════╬═══════╬════════╣
║  1 ║ A      ║ X       ║      E  ║ 2.13     ║ 1.25  ║ 9.81   ║
║  2 ║ A      ║ Y       ║      F  ║ 1.93     ║ 1.61  ║ 9.81   ║
║  2 ║ A      ║ X       ║      G  ║ 46.74    ║ 36.93 ║ 9.81   ║
╚════╩════════╩═════════╩═════════╩══════════╩═══════╩════════╝

Sample of the (joined) data in relation to the expected outcome.
╔════╦═════════╦═════════╦═════════╦═════╦═══════╦══════════╦══════╦═══════╗
║ ID ║ Group 1 ║ Group 2 ║ Group 3 ║ Qty ║ Price ║ Discount ║ Rate ║ Cost  ║
╠════╬═════════╬═════════╬═════════╬═════╬═══════╬══════════╬══════╬═══════╣
║  1 ║ A       ║ X       ║ E       ║   2 ║ 1.065 ║        0 ║    1 ║ 0.625 ║
║  2 ║ A       ║ Y       ║ F       ║   5 ║ 0.193 ║        0 ║    1 ║ 0.161 ║
║  2 ║ A       ║ Y       ║ F       ║   5 ║ 0.193 ║        0 ║    1 ║ 0.161 ║
║  2 ║ A       ║ X       ║ G       ║   1 ║ 20.74 ║        0 ║    1 ║ 20.93 ║
║  2 ║ A       ║ X       ║ G       ║   1 ║ 13    ║        0 ║    2 ║ 16    ║
╚════╩═════════╩═════════╩═════════╩═════╩═══════╩══════════╩══════╩═══════╝

You can use a `subquery` for `profit` – Arun Palanisamy Nov 04 '19 at 14:22 — Arun Palanisamy, Nov 04 '19 at 14:22

score 0 · Accepted Answer · answered Nov 04 '19 at 14:27

since you don't use for reassign .. looking to your code .. you query seems a normal aggreated query so try repeat the code

  select  a.ID
  , c.Group1
  , a.group2
  , b.group3
  , sum( a.Qty * a.Price *  (100 - a.discount) / 100 *  a.Rate) ) as Turnover
  , sum( a.Qty *  a.Cost ) as Cost
  , sum( a.Qty * a.Price *  (100 - a.discount) / 100 *  a.Rate) ) - sum( a.Qty *  a.Cost ) Profit
 from a
 INNER JOIN b ON a.id=b.id 
 INNER JOIN c ON a.id2=c.id
 group by a.ID, c.Group1,  a.group2, b.group3

or if you don't like repated code use a subqyery

  select id, group1, group2, group3, Turnover, Cost, Turnover - Cost as Profit 
  from (
    select  a.ID
    , c.Group1
    , a.group2
    , b.group3
    , sum( a.Qty * a.Price *  (100 - a.discount) / 100 *  a.Rate) ) as Turnover
    , sum( a.Qty *  a.Cost ) as Cost
    , sum( a.Qty * a.Price *  (100 - a.discount) / 100 *  a.Rate) ) - sum( a.Qty *  a.Cost ) 
   from a
   INNER JOIN b ON a.id=b.id 
   INNER JOIN c ON a.id2=c.id
   group by a.ID, c.Group1,  a.group2, b.group3
  ) t

Thanks scaisEdge, I hadn't considered the below option. However I'm still curious why using a variable wouldn't work here. In MSSQL and Redshift it'spossible to use the alias of a calculated column in further calculations later in the query, and Iwas hoping that with defined variables to do the same in MySQL. So was wondering why the use of a variable is not suitable for this kind of aggregations here. — Arjan Pelle, Nov 04 '19 at 14:33
@ArjanPelle which version of mysql you are using ???' .. if you work with mysql version before 5.7 this is related to the causal value for improper use of group by ... (you should not use not aggreated value not mentioned in group by) .. hope is clear .. — ScaisEdge, Nov 04 '19 at 14:37

Calculated MySQL column using variable not returning right result

1 Answers1