John Cobby John Cobby - 1 month ago 10
SQL Question

SQL SUM OVER PARTITION BY Calculate % of group

Can anyone tell me why the below won't compile?

I'm trying to calculate what % the 'M3' marked transaction is of the whole batch.

I think it would be easy enough if there was only ever one batch, but sometimes there will be multiple.

Whole code is below:

SELECT
[Batch],
[DocNum],
[Date],
[Time],
[Location],
[ItemCode],
[Brand],
[Quantity],
CASE
WHEN [Variance] IS NULL
THEN ''
ELSE [Variance]
END AS [Variance],
CASE
WHEN [Comments] IS NULL
THEN ''
ELSE [Comments]
END AS [Comments],
CASE
WHEN [Variance] = 'M3'
THEN(
(
(SELECT
ABS([Quantity])
FROM #BatchReport
WHERE [Variance] = 'M3')
/
(SELECT
SUM([Quantity])
FROM #BatchReport
WHERE [Quantity] > 0)
) * 100) OVER (PARTITION BY [Batch])
ELSE '0'
END AS [Pct Loss],
SUM([Quantity]) OVER (PARTITION BY [Batch]) AS [Difference],
((SUM([Quantity]) - MAX([Quantity])) * 100) / MAX([Quantity]) OVER (PARTITION BY [Batch]) AS [Pct Difference]


FROM #BatchReport
WHERE [DocNum] IS NOT NULL
GROUP BY [Batch], [DocNum], [Date], [Time], [Variance], [Brand], [Comments], [Quantity], [Location], [ItemCode]
ORDER BY [Batch], [Date], [Time]


Error text:
Incorrect syntax near the keyword 'OVER'.


The error vanishes if I remove this part:

CASE
WHEN [Variance] = 'M3'
THEN(
(
(SELECT
ABS([Quantity])
FROM #BatchReport
WHERE [Variance] = 'M3')
/
(SELECT
SUM([Quantity])
FROM #BatchReport
WHERE [Quantity] > 0)
) * 100) OVER (PARTITION BY [Batch])
ELSE '0'
END AS [Pct Loss]


Sample of something like what I'm trying to get:
enter image description here

Thanks!

Answer

Your expression logic is basically incomprehensible. I am guessing you want some sort of ratio between "M3" values and overall values. My best guess is:

(100.0 * SUM(CASE WHEN [Variance] = 'M3' THEN ABS([Quantity]) END) /
 SUM(CASE WHEN Quantity > 0 THEN Quantity END ) OVER (PARTITION BY Batch)
) as [Pct Loss]

However, your GROUP BY is so complex, that I would be surprised if your query did what you want. I think you should post another question with sample data and desired results.

Comments