SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table transaction_file 
  (ID int, AGENT varchar(100), AMT int);
insert into transaction_file values
  (1,'A',40),
  (2,'B',38),
  (3,'A',40);
3 rows affected

create table transaction_product
  (ID int, PARENT_FILE int);
insert into transaction_product values
  (11,1),
  (12,1),
  (13,3),
  (14,3),
  (15,2),
  (16,2);
6 rows affected

SELECT agent, SUM(amt) AS sum_amt
FROM transaction_file
GROUP BY agent;


agent
sum_amt


A
80

B
38


WITH Sums AS 
  (SELECT 
     agent, 
     SUM(amt) AS sum_amt
   FROM transaction_file
   GROUP BY agent)
SELECT
  f.agent,
  s.sum_amt,
  COUNT(p.id) AS product_count
FROM 
  transaction_file f
  INNER JOIN transaction_product p 
    ON p.parent_file = f.id
  INNER JOIN sums s
    ON f.agent = s.agent
WHERE f.id IN (1, 2, 3)
GROUP BY 
  f.agent, 
  s.sum_amt;


agent
sum_amt
product_count


A
80
4

B
38
2