CODEWITHSUNDEEP
sqlselectsumsubquery
Naxxaryl
Naxxaryl

Reputation: 23

SQL - Calculating a sum for each row in a nested select statement

this probably has a rather simple solution but I've been struggling a while now and can't find a solution due to being very new to SQL. I'm currently writing a view in which I want to add a nested select statement to calculate a KPI for each row using a SUM function. The SUM works fine, but it sums all values instead of the values in each row. I've tried using a CTE and window function but I can't get it to work properly.

My code is:

SELECT
some variables as some aliases
...
, (SELECT SUM([EK_NETTO]+[F_PORTO]+[F_DIFFERENZ_OK])
        FROM [RDWH].[dbo].[F_WARENEINKAUF]
        WHERE [REKO_ID] IN('FAKTRG', 'OFFRG', 'PORTO', 'DIFFOK')
    ) as [gross_purchase_amount]
FROM [RDWH].[dbo].[F_WARENEINKAUF]

This is the result:

https://i.sstatic.net/dg3Nl.png

As you can see, the value is the same for each supplier.

Any help is greatly appreciated!

Upvotes: 1

Views: 1395

Answers (1)

LukStorms
LukStorms

Reputation: 29677

It's probably more efficient if you join to the summary.

The queries below assume that the common field is the local_supplier_code.

SELECT
...
, q.[gross_purchase_amount]
FROM [RDWH].[dbo].[F_WARENEINKAUF] t
LEFT JOIN 
(
    SELECT local_supplier_code
    , SUM([EK_NETTO]+[F_PORTO]+[F_DIFFERENZ_OK]) AS gross_purchase_amount
    FROM [RDWH].[dbo].[F_WARENEINKAUF]
    WHERE [REKO_ID] IN ('FAKTRG', 'OFFRG', 'PORTO', 'DIFFOK')
    GROUP BY local_supplier_code
) q ON q.[local_supplier_code] = t.[local_supplier_code]

Or you could use an APPLY

SELECT
...
, a.[gross_purchase_amount]
FROM [RDWH].[dbo].[F_WARENEINKAUF] t
OUTER APPLY 
(
    SELECT SUM([EK_NETTO]+[F_PORTO]+[F_DIFFERENZ_OK]) AS gross_purchase_amount
    FROM [RDWH].[dbo].[F_WARENEINKAUF] t2
    WHERE t2.[local_supplier_code] = t.[local_supplier_code]
      AND [REKO_ID] IN ('FAKTRG', 'OFFRG', 'PORTO', 'DIFFOK')
) a

Or use a SUM OVER
However, this method only works if the summed records are in the result.

SELECT
...
, SUM(CASE
      WHEN [REKO_ID] IN ('FAKTRG', 'OFFRG', 'PORTO', 'DIFFOK')
      THEN [EK_NETTO]+[F_PORTO]+[F_DIFFERENZ_OK]
      ELSE 0
      END) OVER (PARTITION BY [local_supplier_code]) AS [gross_purchase_amount]
FROM [RDWH].[dbo].[F_WARENEINKAUF] t

Upvotes: 1

Related Questions