Subqueries and Joins for Running Aggregates

SELECT S1.empid, S1.qty, S1.val, 

   SUM(S2.qty) AS sumqty, 

   AVG(S2.qty) AS avgqty, 

   SUM(S2.val) AS sumqty, 

   AVG(S2.val) AS avgval

FROM dbo.Sales AS S1

   JOIN dbo.Sales AS S2

       ON S2.empid = S1.empid

       AND S2.dt 
GROUP BY S1.empid, S1.dt, S1.qty, S1.val;