SQL statement to SUM values in one column by distinct values in another column while grouping by a third column?

I am trying to write a SQL statement to report the total amount an agent has sold and a count of the products they have sold from another table. I'm having difficulty getting the SUM data to come out right. There is a parent File record that has several child product records to it.

Sample schema:

transaction_file

ID	AGENT	AMT
1	A	40
2	B	38
3	A	40

transaction_product

ID	PARENT_FILE
11	1
12	1
13	3
14	3
15	2
16	2

Joining the tables comes out as

FILE_ID	AGENT	AMT	PRODUCT_ID
1	A	40	11
1	A	40	12
3	A	40	13
3	A	40	14
2	B	38	15
2	B	38	16

Desired Results

AGENT	SUM AMT	PRODUCT COUNT
A	80	4
B	38	2

I need to group by the Agent show the product count then sum the AMT by the distinct FILE_ID.

I've tried my SQL state a couple of ways and can't get the sum of the AMT to come out right. My current SQL statement:

SELECT
    File.agent,
    COUNT(Product.id) product_count,
    SUM(DISTINCT File.amt) sum_amt

FROM transaction_file File

INNER JOIN transaction_product Product ON Product.parent_file = File.id

WHERE File.id IN (1, 2, 3)

GROUP BY File.agent

Produces:

AGENT	SUM AMT	PRODUCT COUNT
A	40	4
B	38	2

AND

SELECT
    File.agent,
    COUNT(Product.id) product_count,
    SUM(File.amt) sum_amt

FROM transaction_file File

INNER JOIN transaction_product Product ON Product.parent_file = File.id

WHERE File.id IN (1, 2, 3)

GROUP BY File.agent

Produces:

AGENT	SUM AMT	PRODUCT COUNT
A	120	4
B	76	2

I've tried a couple of subqueries, but I can't get those to work at all.

Any help or suggestion would be appreciated!

Solution

One option is to use a CTE to build the required sum per agent and then JOIN the result to your main query.

This will be the query that selects the sum per agent:

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

So the entire query will be this one:

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;

This will be the result of the query for your sample data:

AGENT	SUM AMT	PRODUCT COUNT
A	80	4
B	38	2

See this fiddle example