"Unable to execute aggregate function on failure or subquery" on SQL Server
Select A.nEvrakKalemID As ID,
A.cEvrakKalem As [Inventory Name],
SUM((IsNull((Select SUM(B.nMiktar) Where B.nEvrakTip = 3 or B.nEvrakTip = 7),0)) - (IsNull((Select SUM(B.nMiktar) Where B.nEvrakTip = 4 or B.nEvrakTip = 8),0))) As Inventory
From MasrafAnaliz.dbo.EvrakKalem A
Left Outer Join MasrafAnaliz.dbo.EvrakKalemHareket B
On B.nKodEvrakKalem = A.nEvrakKalemID
Group By A.nEvrakKalemID, A.cEvrakKalem, B.nMiktar
Order By A.nEvrakKalemID
In the above question, I want to summarize the column Inventory
and group it by column nEvrakKalemID
. But it doesn't work. Thank you in advance for your help.
+3
source to share
2 answers
Try the following:
Select A.nEvrakKalemID As ID,
A.cEvrakKalem As [Inventory Name],
(SUM(CASE WHEN B.nEvrakTip = 3 or B.nEvrakTip = 7
THEN (IsNull(B.nMiktar,0))
ELSE 0
END) -
SUM(CASE WHEN B.nEvrakTip = 4 or B.nEvrakTip = 8
THEN IsNull(B.nMiktar,0)
ELSE 0
END)) As Inventory
From MasrafAnaliz.dbo.EvrakKalem A
Left Outer Join MasrafAnaliz.dbo.EvrakKalemHareket B On B.nKodEvrakKalem = A.nEvrakKalemID
Group By A.nEvrakKalemID, A.cEvrakKalem, B.nMiktar
Order By A.nEvrakKalemID
+1
source to share
I think your request should be
SELECT
A.nEvrakKalemID AS ID,
A.cEvrakKalem AS [Inventory Name],
SUM(ISNULL((SELECT
B.nMiktar
WHERE B.nEvrakTip = 3
OR B.nEvrakTip = 7), 0) - ISNULL((SELECT
B.nMiktar
WHERE B.nEvrakTip = 4
OR B.nEvrakTip = 8), 0)) AS Inventory
FROM MasrafAnaliz.dbo.EvrakKalem A
LEFT OUTER JOIN MasrafAnaliz.dbo.EvrakKalemHareket B
ON B.nKodEvrakKalem = A.nEvrakKalemID
GROUP BY A.nEvrakKalemID,
A.cEvrakKalem,
B.nMiktar
ORDER BY A.nEvrakKalemID
Using CASE
would be the best option.
0
source to share