Unable to get Targeted total sales amount

Original Problem

Using AdventureWorks2008R2, write a query to return the salesperson id, number of unique products sold, highest order value, total sales amount, and top 3 orders for each salesperson.

Use TotalDue in SalesOrderHeader when calculating the highest order value and total sales amount. The top 3 orders have the 3 highest total order quantities. If there is a tie, the tie must be retrieved. Exclude orders which don't have a salesperson for this query. Return only the salespersons whose total sales were greater than $9800000.

Return the order value and total sales as int. Sort the returned data by SalesPersonID. The returned data should have a format as displayed below. Use the sample format for formatting purposes only.

Result

Code:

WITH SalesData AS 
(
    SELECT 
        soh.SalesPersonID,
        soh.SalesOrderID,
        sod.ProductID,
        soh.TotalDue
    FROM
        Sales.SalesOrderHeader soh
    JOIN 
        Sales.SalesOrderDetail sod ON sod.SalesOrderID = soh.SalesOrderID
    WHERE
        soh.SalesPersonID IS NOT NULL
),
RankedSalesData AS 
(
    SELECT 
        SalesPersonID,
        SalesOrderID,
        TotalDue,
        ROW_NUMBER() OVER (PARTITION BY SalesPersonID ORDER BY TotalDue DESC) AS SalesRank
    FROM
        (SELECT DISTINCT SalesPersonID, SalesOrderID, TotalDue FROM SalesData) AS UniqueSalesData
),
SalesSummary AS 
(
    SELECT
        SalesPersonID,
        COUNT(DISTINCT ProductID) AS UniqueProductsSold,
        Cast(MAX(TotalDue)as int) AS HighestOrderValue,
        Cast(SUM(TotalDue) as int) AS TotalSalesAmount
    FROM
        SalesData
    GROUP BY
        SalesPersonID
)
SELECT
    SS.SalesPersonID,
    SS.UniqueProductsSold,
    SS.HighestOrderValue,
    SS.TotalSalesAmount,
    STUFF((SELECT ',' + CAST(R.SalesOrderID AS VARCHAR)
           FROM RankedSalesData R
           WHERE R.SalesPersonID = SS.SalesPersonID AND R.SalesRank <=3 
           FOR XML PATH('')), 1, 1, '') AS Top3Orders
FROM
    SalesSummary SS
WHERE
    SS.TotalSalesAmount > 9800000
ORDER BY
    SS.SalesPersonID;

My result:

If you see the TotalSales Amount, it is multiplying multiple orders into the final amount and not matching the answer. I don't know where I am doing it wrong. I already tried to debug it with ChatGPT. So, Please save your answer from that. If you can explain in detail where I go wrong, It will be greatly appreciated in my academic career. I thank you ahead.

You will have to change the object names in this. This code assumes the existence of two tables:

CREATE TABLE InvoiceItems (InvoiceItemID BIGINT IDENTITY(1,1) NOT NULL, InvoiceID BIGINT NOT NULL, Sequence INT NULL, Quantity INT NULL, Cost DECIMAL(7, 2) NULL, Price DECIMAL(7, 2) NULL, ProductID BIGINT NULL)
CREATE TABLE Invoices (InvoiceID BIGINT IDENTITY(1,1) NOT NULL, InvoiceDateTimeUTC DATETIME2(7) NULL, CustomerID BIGINT NULL, EmployeeID BIGINT NULL, StoreID INT NULL)

(These are part of a script I maintain which generates completely randomized sales data, with proper constraints and all sorts of fun)

The code should provide an example of a way to produce the results you're looking for.

USE RandomSales;

;WITH Sales AS (
SELECT i.InvoiceID, i.InvoiceDateTimeUTC, i.CustomerID, i.EmployeeID, i.StoreID, ii.InvoiceItemID, ii.Sequence, ii.Quantity, ii.Cost, ii.Price, ii.ProductID, 
       ii.Price*ii.Quantity AS ItemTotal, 
       ii.Cost*ii.Quantity AS ItemValue, 
       SUM(ii.Price*ii.Quantity) OVER (PARTITION BY i.InvoiceID ORDER BY @@SPID) AS InvoiceTotal,
       SUM(ii.Cost*ii.Quantity) OVER (PARTITION BY i.InvoiceID ORDER BY @@SPID) AS InvoiceValue,
       SUM(ii.Quantity) OVER (PARTITION BY i.InvoiceID ORDER BY @@SPID) AS QuantityTotal
  FROM dbo.Invoices i
    INNER JOIN dbo.InvoiceItems ii
      ON ii.InvoiceID = i.InvoiceID
), QtyRankByEmployee AS (
SELECT EmployeeID, STRING_AGG(InvoiceId,', ') AS Invoices
  FROM (
        SELECT Sales.EmployeeID, Sales.InvoiceID, MAX(Sales.QuantityTotal) AS Qty, RANK() OVER (PARTITION BY Sales.EmployeeID ORDER BY MAX(Sales.QuantityTotal) DESC) AS QtyRank
          FROM Sales
         GROUP BY Sales.EmployeeID, Sales.InvoiceID
       ) a
 WHERE a.QtyRank <= 3
 GROUP BY a.EmployeeID
), EmployeeTotal AS (
SELECT EmployeeID, COUNT(DISTINCT ProductID) AS TotalUniqueProducts, SUM(InvoiceValue) AS TotalValue, SUM(InvoiceTotal) AS TotalSales
  FROM Sales s
 GROUP BY EmployeeID
)

SELECT e.EmployeeID, e.TotalUniqueProducts, e.TotalValue, e.TotalSales, q.Invoices
  FROM EmployeeTotal e
    LEFT OUTER JOIN QtyRankByEmployee q
      ON e.EmployeeID = q.EmployeeID