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

transaction_product

Joining the tables comes out as

Desired Results

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:

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:

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

Any help or suggestion would be appreciated!

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:

See this fiddle example