GROUP BY not returning correct totals

Question

I have this query which I have been messing with and cannot seem to see what to change in order to receive the results I want.

I want to sum sales by Emp_ID by day, but only sum the ones over $10000 for that day. Below is what I currently have

SELECT
    Emp_ID,
    sum(SaleA+SaleB) as TotalSales,
    sum(SaleA+SaleB-CommA-CommB) as TSalesAftComm,
    count(Emp_ID) as NumOfSales,
    SaleDate
FROM
    Sales (nolock)
WHERE
    SaleDate>='2014-03-15 00:00:00'
GROUP BY 
    SaleDate, Emp_ID
HAVING
    sum(SaleA+SaleB) > 10000
ORDER BY
    SaleDate

I know that in my select and group by (Emp_ID) it will group by date and also Emp_ID for that date. It seems if I remove the Emp_ID in the SELECT and GROUP BY area it adds all sales for that day even the ones below $10000.

Below are the results I get

Emp_ID | TotalSales | TSalesAftComm | NumOfSales | SaleDate
   1      10897.65      10000             6        2014-03-15 00:00:00.000
   1      18897.65      17800             8        2014-03-15 00:00:00.000
   2      10797.65      10000             5        2014-03-15 00:00:00.000
   1      10897.65      10000             6        2014-03-16 00:00:00.000

I would like to see the results as

       | TotalSales | TSalesAftComm | NumOfSales | SaleDate
          40592.95      37800             19        2014-03-15 00:00:00.000
          10897.65      10000              6        2014-03-16 00:00:00.000

Thank you for any help or direction you can provide.

Answer 1

Don't have SQL Server 2000 to test with, but you should be able to get it done using a plain subquery, something like;

SELECT SUM(TotalSales) TotalSales, SUM(TSalesAftComm) TSalesAftComm,
       SUM(NumOfSales) NumOfSales, SaleDate
FROM (
  SELECT
      Emp_ID,
      sum(SaleA+SaleB) as TotalSales,
      sum(SaleA+SaleB-CommA-CommB) as TSalesAftComm,
      count(Emp_ID) as NumOfSales,
      SaleDate
  FROM Sales (nolock)
  WHERE SaleDate>='2014-03-15 00:00:00'
  GROUP BY SaleDate, Emp_ID
  HAVING
      sum(SaleA+SaleB) > 10000
) z
GROUP BY SaleDate
ORDER BY SaleDate