Multiply and SUM after performing INNER JOIN

Question

I have 2 tables named orders and order_details.

The orders table contains the orders data like id, employee_id, customer_id, order_date, etc.

And the order_details contains id, order_id, product_id, quantity, unit_price, etc.

I have made an inner join from both of this tables to gather the employee_id, quantity and unit_price from this tables.

I made this to gather the quantity * unit_price that each employee_id sold.

But I have to SUM all of the result from (quantity * unit_price) so i have the total amount that each employee sold.

This is a preview of the table:

   Employee_id     total_price
       1             250.000
       1              10.00
       1             728.000
       2              72.00
       2               8.00
       2              900.00
       3              222.00
       3              123.00

I would need to sum the total_price from all the employee_id so it looks like this:

  Employee_id      total_price
      1              10222.00
      2               982.00
      3               339.00

This is the code I have:

select  o.employee_id,
        od.quantity*od.unit_price as total_price
        from orders AS o
        inner join order_details AS od
        on o.id = od.id;

What I attempted:

select  o.employee_id,
        od.quantity*od.unit_price as total_price,
        SUM (total_price)
        from orders AS o
        inner join order_details AS od
        on o.id = od.id
        group by employee_id;

GMB GMB · Accepted Answer · 2020-10-21T16:53:38

Presumably, you want:

select o.employee_id, sum(od.quantity*od.unit_price) tota_price
from orders as o
inner join order_details as od on o.id = od.order_id
group by o.employee_id;

Rationale:

the multplication goes within aggregate function SUM()
the join condition between the two tables needs to be fixed: the relation seems to be between orders(id) and order_details(order_id) rather than order_details(id)

Multiply and SUM after performing INNER JOIN

2 Answers