multiple select statement sql returns 2 columns: how to add these with eachother-CodePudding

I have this following query:

SELECT 
    (SELECT TOP 1 SUM (Price) OVER () AS requestedParts
     FROM Claim
     INNER JOIN ClaimCrossTire ON Claim.ID = ClaimCrossTire.ClaimID
     INNER JOIN Tire ON ClaimCrossTire.TireID = Tire.ID
     WHERE Claim.ID = 386160) AS requestedParts,
    (SELECT TOP 1 SUM(Cost * nCost) OVER () AS requestedLabour
     FROM Claim c 
     INNER JOIN ClaimCrossCostItem ccci ON c.ID = ccci.ClaimID
     INNER JOIN CostItem ci ON ccci.CostItemID = ci.ID
     WHERE ci.CostItemTypeID = 1 AND ClaimID = 386160) AS requestedLabour

This returns a result like:

requestedParts  requestedLabour
-------------------------------
144.000          291.000000

Now, I would like to add these two columns with eachother and create a third column called something like total, where requestedParts and requestedLabour are added so in this case it would be total: 435.000. Anyone know if this is possible?

CodePudding user response：

You can place these two subqueries in the FROM clause and cross-join them.

I'm not able to tell without seeing your schema, but you may even be able to combine the two into a single aggrgetation

I also removed the window functions, as using standard aggregation got the same result in this instance

SELECT
  rp.requestedParts,
  rl.requestedLabour,
  rp.requestedParts   rl.requestedLabour AS total
FROM
(
     SELECT SUM (Price) AS requestedParts
     FROM Claim
     INNER JOIN ClaimCrossTire ON Claim.ID = ClaimCrossTire.ClaimID
     INNER JOIN Tire ON ClaimCrossTire.TireID = Tire.ID
     WHERE Claim.ID = 386160
) AS rp
CROSS JOIN
(
     SELECT SUM(Cost * nCost) AS requestedLabour
     FROM Claim c 
     INNER JOIN ClaimCrossCostItem ccci ON c.ID = ccci.ClaimID
     INNER JOIN CostItem ci ON ccci.CostItemID = ci.ID
     WHERE ci.CostItemTypeID = 1 AND ClaimID = 386160
) AS rl;

CodePudding user response：

Perhaps place these sub-queries inside an outer select statement that lists requestedParts, requestetLabour and the total as well?