Geek asks and answers

Calculation field amount

I have two tables:

  • table1 (a, b, c)
  • table2 (a, d, f)

I want to get for each b to get the sum of the multiplication of the corresponding d and f

table 1 data

a   b      c
-------------
1   hello  3
2   bye    4

table 2 data

a  d  f
--------
1  5  3
1  2  4
2  1  3
2  2  3

result: hi: 5 * 3 + 2 * 4, bye: 1 * 3 + 2 * 3
My request:

  SELECT t1.a, 
         t2.d * t2.f AS m, 
         SUM(m)   
    FROM table1 AS t1, 
         table2 AS t2 
   WHERE t1.a = t2.a 
GROUP BY t1.b

So what is wrong here? In mysql I get # 1054 - Unknown column "m" in the "list of fields"

+3
source share
2 answers

Try:

SELECT t1.a, t2.d*t2.f AS m, SUM(t2.d*t2.f)
FROM table1 AS t1, table2 AS t2 
WHERE t1.a=t2.a GROUP BY t1.b

(I. expand the nickname)

+5
source

You cannot refer to a column alias in the same SELECT clause - you either need to reproduce the formula:

  SELECT t1.a, 
         t2.d * t2.f AS m, 
         SUM(t2.d * t2.f)   
    FROM table1 AS t1 
    JOIN table2 AS t2 ON t1.a = t2.a 
GROUP BY t1.b

.. or use a view / inline view:

SELECT x.a,
       x.m, 
       SUM(x.m)
  FROM (SELECT t1.a, 
               t2.d * t2.f AS m
          FROM table1 AS t1 
          JOIN table2 AS t2 ON t1.a = t2.a 
      GROUP BY t1.b) x

MySQL - GROUP BY (HAVING ORDER BY ). ORDER BY.

+4

More articles:


All Articles