CODEWITHSUNDEEP
mysql
collado.17
collado.17

Reputation: 33

mysql sum results after each sum

Hello I'm trying to sum the result of two mysql result.

This is my code, it doesn't work.

SELECT 
     a.* ,
    b.* , 
    SUM(b.price) as price,
    SUM(b.tax) as tax,
    price + tax as price_tax --> error
FROM (SELECT  name , item_id 
     FROM items)a
LEFT JOIN (SELECT item_id , price , tax
         FROM bank
        )b
ON a.item_id = b.item_id 
GROUP BY item_id

Upvotes: 0

Views: 40

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

First

The SQL engine don't know the column alias in select clause during (the specific) query execution so you can't use alias you must repeat the code 

      SELECT 

        SUM(b.price) as price,
        SUM(b.tax) as tax,
        SUM(b.price) +  SUM(b.tax) as  price_tax 
        FROM (
            SELECT  name , item_id 
            FROM items
        )a
        LEFT JOIN (
        SELECT item_id , price , tax

        FROM bank
        )b
        ON a.item_id = b.item_id 
        GROUP BY item_id

Second

in some mysql version is not allowed the use of aggregation function and select column not mentioned in group by so you must remove the column not mention in group by (or add fake aggreation function ) for the others columns 

      SELECT 
        a.item ,
        SUM(b.price) as price,
        SUM(b.tax) as tax,
        SUM(b.price) +  SUM(b.tax) as  price_tax 
        FROM (
            SELECT  name , item_id 
            FROM items
        )a
        LEFT JOIN (
        SELECT item_id , price , tax

        FROM bank
        )b
        ON a.item_id = b.item_id 
        GROUP BY item_id

Upvotes: 3

Related Questions