T-SQL average page count for each employee-CodePudding

I have 2 different tables and I would like an average page count per hour for each employee. I am new to subqueries so I'm still trying to wrap my head around them. This is what I have so far but I need the average page count for each employee but I am getting the average total.

SELECT 
    Employee,
    ((SELECT SUM(Pagecount) FROM Table2) / 
     (SELECT SUM(Duration) FROM Table1))
FROM Table1;

CodePudding user response：

You should not need a subquery here, rather you would want to JOIN the tables together.

DECLARE @Table1 TABLE (PrepDate DATE, PickupNumber INT, BoxNumber INT, JobType INT, Duration DECIMAL(5, 2), Employee VARCHAR(100), BoxStatus VARCHAR(5));
DECLARE @Table2 TABLE (ScanDate DATE, PickupNumber INT, BoxNumber INT, PageCount INT);

INSERT @Table1 (PrepDate, PickupNumber, BoxNumber, JobType, Duration, Employee, BoxStatus)
VALUES ('20220707', 123, 8, 0, 3.75, 'Jdoe', 'I'),
       ('20220808', 456, 9, 0, 5.25, 'Msmith', 'C');

INSERT @Table2 (ScanDate, PickupNumber, BoxNumber, PageCount)
VALUES ('20220807', 123, 8, 525),
       ('20220823', 456, 9, 785);

SELECT t1.Employee, (t2.PageCount / t1.Duration) AS AvgPageCount
FROM @Table1 t1
INNER JOIN @Table2 t2 ON t2.PickupNumber = t1.PickupNumber
                     AND t2.BoxNumber = t1.BoxNumber;

This will produce:

  Employee   |   AvgPageCount
------------- -----------------
  Jdoe       |   140.000000
  Msmith     |   149.523809