How to summarize the employees by net revenue and not order date? : 8.	The sales director would like to reward the employees with net sales over $150,000 for the years 2015 and 2016 combined. The Sales Manager would like the resulting query to display the following columns: Employee ID, Employee Name (First and Last as one field), Total Net Sales per employee. (Both years should be combined into one number.) [enter image description here][1]Sort largest to smallest.  

We cannot figure out how to combine the revenues by the employees.

    select o.EmpID, EmpFName + ' ' + EmpLastName as "Employee Name",
    sum((salesunitprice*quantitysold)-((salesunitprice*quantitysold)*ItemDiscount)) as "Net Sales"
    from EMPLOYEE e inner join ORDERS o on e.EmpID=o.EmpID
    inner join SALES_INVOICE si on o.OrderID=si.OrderID
    inner join SALES_INVOICE_DETAIL sd on si.SalesInvoiceID=sd.SalesInvoiceID
    group by o.EmpID, EmpFName + ' ' + EmpLastName, OrderDate
    having OrderDate between '2015-01-01' and '2016-12-31' 
    order by [Employee Name]

 

I expected the output to be the total per employee, but it is broken out by individual order dates instead of summing the total net sales per employee.  Please help!


  [1]: https://i.stack.imgur.com/MEkfH.png