can anyone please help to create a query for this, I tried a lot but did not succeed

create a SQL query selecting all employees, invoice totals for the customers they handle.

CodePudding user response：

Assuming that a single invoice is calculated by UnitPrice * Quantity, the following query should work:

select EmployeeId, sum(invoice) InvoicesTotal
from 
    (select EmployeeId, SupportRepId, CustomerId
    from employees e 
    left join customers c
    on e.EmployeeId = c.SupportRepId) ec
left join 
    (select CustomerId, sum(UnitPrice * Quantity) invoice
    from invoices i
    join
    invoice_items ii
    on i.InvoiceId = ii.InvoiceId 
    group by CustomerId) ci
on 
    ec.CustomerId = ci.CustomerId
group by EmployeeId