SQL - Unable to correctly calculate SUM

Question

I tried for several days this SQL statement.

I have a database for sales, and all I need to do is:

SELECT 
  SUM(orders.total) as total, orders.transaction_date as date, 
  orders.id as orderid, orders.employee_id as empl 
from orders GROUP by orders.employee_id

This request is beautiful, it gives me everything I need. However, I need to add the quantity that has been sold so far, which matches this query:

SELECT order_id, SUM(quantity) 
FROM order_items 
Group By order_id

It's also good. Both work fine, but I need them in a single sql statement.

When i try

SELECT 
  SUM(order_items.quantity), 
  SUM(orders.total) as total, 
  orders.transaction_date as date, 
  orders.id as orderid, 
  orders.employee_id as empl 
from 
  orders, order_items 
where 
  order_items.order_id = orders.id 
GROUP by 
  orders.employee_id

Everything seems correct, except for the general. I have no idea why this is happening.

This is db

orders:

id | employee_id | transaction_date | total

order_items:

order_id | quantity

Answer 1

You need to combine the two tables separately before joining them:

select sum(oi.quantity), sum(o.total) as total, 
       o.employee_id as empl 
from orders o join
     (select oi.order_id, sum(oi.quantity) as quantity 
      from order_items oi
      group by oi.order_id
     ) oi
     on oi.order_id = o.id 
group by o.employee_id;

select. , (, ) .

Answer 2

order_id, orders.employee_id. order_items ? GROUP BY. , employee_id orders order_items. .

Answer 3

GROUP BY , .

SELECT 
  SUM(order_items.quantity) as quantity, 
  SUM(orders.total) as total, 
  orders.transaction_date as date, 
  orders.id as orderid, 
  orders.employee_id as empl 
from 
  orders
LEFT JOIN
  order_items
ON
  orders.order_id = order_items.order_id
where 
  order_items.order_id = orders.id 
GROUP by 
  orders.employee_id, orders.order_id

- . employee_id. , , .