ms access - SQL Get total order of managers -

so in database employees make sales , each employee has manager.

im trying figure out how calculate sales total manager.

i found out how sales of each employee im not sure how manager.

select employee.name, sum(orderitem.quantity*orderitem.saleprice) (employee inner join [order] on employee.eid = order.eid) inner join orderitem on       order.orderid = orderitem.orderid group employee.name; 

do need order manager? or have clause list managers?

assuming 1 level of manager employee exists... assumes managers don't have sales need added in.

select e2.name, sum(orderitem.quantity*orderitem.saleprice) employee  inner join [order] on employee.eid = order.eid inner join orderitem on order.orderid = orderitem.orderid inner join employee e2 on e2.eid = e2.managerid group e2.name; 

this joins employee table again managers.

if need include manager sales along employee sales under same manager need use inner join similar follows

inner join employee e2 on e2.eid = coalese(e2.managerid, e2.eid) 

coalese grabs first non-null value in set of variables. in case, since managerid field null on records managers, substitute eid inplace of manager id allowing sales aggregrated in well.

however again depends on being 1 level deep w/ managers , having managers managerid null.

`