CODEWITHSUNDEEP
sql-server
Şükrü Akkoç
Şükrü Akkoç

Reputation: 33

'Can not perform an aggregate function on an aggregate or a subquery' error 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 sum up the Inventory column and group it according to the column nEvrakKalemID. But it does not work. Thank you in advance for your help.

Upvotes: 3

Views: 43

Answers (2)

Sankar
Sankar

Reputation: 7107

I think your query 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 will be the better option.

Upvotes: 0

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this :

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

Upvotes: 1

Related Questions