Add the value of multiple rows in SQL Server where the number of rows can vary-CodePudding

I have two tables, Claim and CostItem with a cross table, where one claim can have multiple cost items.

For example this query:

SELECT 
    CostItem.ID, Cost AS pricePerUnit, nCost AS quantity, Cost * nCost AS total
FROM 
    Claim
INNER JOIN 
    ClaimCrossCostItem ON Claim.ID = ClaimCrossCostItem.ClaimID
INNER JOIN 
    CostItem ON ClaimCrossCostItem.CostItemID = CostItem.ID
WHERE 
    CostItem.CostItemTypeID = 2 AND ClaimID = 1512969

will result in 5 rows like this:

ID      pricePerUnit    quantity    total
--------------------------------------------
3038421    100.000      2.000     200.000000
3038422    130.000      2.000     260.000000
3038423    120.000      1.000     120.000000
3038424    130.000      21.000    2730.000000
3038427    99.000       52.000    5148.000000

Now, I would like to add all of the rows total with each other so it would result in this case in a total of 8458 if all of the total rows are added with each other. And this is just an example, some ClaimIDs will maybe have 2 rows, 1 row or 6 rows and so on. Does anyone know how to solve this problem? The desired output would be simply one column named total where all the rows are added with each other. Thank you.

CodePudding user response：

You may use SUM() as an analytic function here:

SELECT
    ci.ID,
    Cost AS pricePerUnit,
    nCost AS quantity,
    Cost * nCost AS total,
    SUM(Cost * nCost) OVER () AS grandTotal
FROM Claimc 
INNER JOIN ClaimCrossCostItem ccci ON c.ID = ccci.ClaimID
INNER JOIN CostItem ci ON ccci.CostItemID = ci.ID
WHERE ci.CostItemTypeID = 2 AND ClaimID = 1512969;