title: TSQL - aggregate function on an expression containing an aggregate ERROR
body: <p>Hi there I have a procedure like so:</p>
  
  <pre><code>CREATE PROCEDURE dbo.DWS_PROG (
                                  @fromDate datetime = '2012-01-01',
                                  @maxMoney money = 200,
                                  @limit int = 200 
                                )
  AS
  SET NOCOUNT ON; 
  
  SELECT TOP (@limit)
       convert(int,P.P_ID) AS 'ID'
      , max(convert(MONEY,isnull(P.NGp,P.NGz))) AS 'NG'
      , max(convert(MONEY,(CASE WHEN D.Doc_Type = 0x0000000000000016 
                                THEN Cash_PLN 
                                ELSE 0 END))) AS 'Interests'
      , (SELECT TOP 1 convert(VARCHAR, H.Data_zmiany,105) 
           FROM dbo.Pozew_Historia_Stanow H 
          WHERE  H.P_ID = P.P_ID 
            AND H.Stan_nastepny_id = 0x000000000000000D /*PZ*/) AS 'Data Pozwu'
      , max((CASE WHEN Sad_Id=0x0000000000000E08 
                  THEN 'EL' 
                  ELSE 'PA' END)) AS 'TYPE'
      , max((CASE WHEN Cost_Name =0x0000000000000008 
                  THEN convert(varchar, Pay_Date,105) 
                  ELSE NULL END)) AS 'Data pierwszego kosztu wpisu sądowego'
      , (SELECT TOP 1 convert(VARCHAR, H.Data_zmiany,105) 
           FROM dbo.Pozew_Historia_Stanow H 
          WHERE H.P_ID = P.P_ID 
            AND H.Stan_nastepny_id = 0x00000000000000DD /*EGZ-1*/) AS 'Data wszczęcia egzekucji'
      , sum((CASE WHEN D.Doc_Type = 0x0000000000000003 
                   AND D.Pay_Date &gt;= @fromDate 
                   AND D.Pay_Date &lt; DATEADD(mm,DATEDIFF(mm,0,@fromDate) + 1,0) 
                  THEN Cash_PLN 
                  ELSE 0 END)) AS 'M0'
   FROM dbo.Pozew P 
   JOIN dbo.Orders  Z 
     ON Z.Orders_Id = P.Orders_Id 
   JOIN dbo.Docs D 
     ON D.P_ID = P.P_ID
  WHERE ((P.NGp IS NOT NULL AND P.NGp &lt; @maxMoney) OR (P.NGp IS NULL AND P.NGz &lt; @maxMoney))
    AND P.Stan IN ('PZ-PORAŻKA','PZ-PORAŻ-UB','PZ-PSPPOR-SP','PZ-PSPPOR-UP','PZ-PSSSUK-UP','PZ-ZAPŁATA','EGZ-C-ROSZ-1','EGZ-C-ROSZ-2','EGZ-SUKCES-S','EGZ-ZAS-R','EGZ-NZAS-R','EGZ-BS-1C','EGZ-WSZP-1','EGZ-WSZP-2','EGZ-SPL')
    AND (SELECT TOP 1 dbo.Docs.Pay_Date 
           FROM dbo.Docs 
          WHERE dbo.Docs.P_ID = P.P_ID 
            AND dbo.Docs.Cost_Name = 0x0000000000000008 
       ORDER BY dbo.Docs.Pay_Date) &gt;= @fromDate
  GROUP BY P.P_ID
  </code></pre>
  
  <p>This basicly works fine, but I have one error.
  In the last line before JOIN I sum cash for all Documents that have type 0x00...03 and Pay_date is between @param and end of month for param. Simple and works :)</p>
  
  <p>But I must replace @fromDate with subquery:</p>
  
  <pre><code>SELECT TOP 1 dbo.Docs.Pay_Date 
    FROM dbo.Docs 
   WHERE dbo.Docs.P_ID = P.P_ID 
     AND dbo.Docs.Cost_Name = 0x0000000000000008 
     AND dbo.Docs.Pay_Date &gt;= @fromDate  
   ORDER BY dbo.Docs.Pay_Date
  </code></pre>
  
  <p>So that for every document query will take the first date with specific type of payment and then it will sum all payments starting from this date to end of month.</p>
  
  <p>SUM should look like this:</p>
  
  <pre><code>SUM((CASE WHEN D.Doc_Type = 0x0000000000000003 
             AND D.Pay_Date &gt;= 
                 (SELECT TOP 1 dbo.Docs.Pay_Date 
                    FROM dbo.Docs 
                   WHERE dbo.Docs.P_ID = P.P_ID 
                     AND dbo.Docs.Cost_Name = 0x0000000000000008 
                     AND dbo.Docs.Pay_Date &gt;= @fromDate  
                   ORDER BY dbo.Docs.Pay_Date)
  
             AND D.Pay_Date &lt; DATEADD(mm, DATEDIFF(mm, 0, 
                (SELECT TOP 1 dbo.Docs.Pay_Date 
                   FROM dbo.Docs 
                  WHERE dbo.Docs.P_ID = P.P_ID 
                    AND dbo.Docs.Cost_Name = 0x0000000000000008 
                    AND dbo.Docs.Pay_Date &gt;= @fromDate 
                  ORDER BY dbo.Docs.Pay_Date)) + 1,0) 
           THEN Cash_PLN 
           ELSE 0 END)) AS 'M0'
  </code></pre>
  
  <p>But after doing that Management Studio throws an error:</p>
  
  <blockquote>
    <p>Cannot perform an aggregate function on an expression containing an aggregate or a subquery.</p>
  </blockquote>
  
  <p>I'm trying to solve that for almost 2 days.
  Any ideas?</p>
  
  <p>Sorry for not showing structure of tables, but the database is too big and I only need a way to solve that error :)</p>
  
  <p>My tables look like so:
  Table Pozew: id, name, etc</p>
  
  <p>Table Docs: docid, p_id (key from Pozew table), doc_type, cost_name, pay_date, cash_PLN</p>
  
  <p>And sample records look like so:</p>
  
  <p>Docs:</p>
  
  <p>1,1,15,8,'2011-02-04',400<br/>
  1,1, 3,5,'2011-02-09',0<br/>
  1,1, 3,5,'2011-02-12',0<br/>
  1,1, 3,5,'2011-02-04',0<br/></p>
  
  <p>Simple scenario:
  we add a record to docs table saying we have 1th document with cost_name=0x8, then other documents are added but with Doc_Type = 0x3.</p>
  
  <p>So we want to get all documents that have doc_Type =0x3 and were added between creation date of first document with cost_name=0x8 and end of month.
  But we must group them for every Pozew_id.</p>
  
  <p>Sorry for my pour english :/</p>
  
  <p>I have created a simple query that will show my problem more clearly:</p>
  
  <pre><code>DECLARE @fromDate varchar
  SET @fromDate = '2011-06-01'
  SELECT TOP 100
  sum((CASE WHEN D.Typ_dokumentu = 0x0000000000000003 
            AND D.Data_platnosci &gt;= 
            (SELECT TOP 1 Data_platnosci
             FROM Dokumenty
             WHERE Pozew_Id = P.Pozew_Id 
             AND Nazwa_kosztu = 0x0000000000000008
             AND Data_platnosci &gt;= @fromDate 
             GROUP BY Pozew_Id) 
            AND Data_platnosci &lt; DATEADD(mm,DATEDIFF(mm,0,
            (SELECT TOP 1 Data_platnosci 
             FROM Dokumenty 
             WHERE Pozew_Id = P.Pozew_Id
             AND Nazwa_kosztu = 0x0000000000000008
             AND Data_platnosci &gt;= @fromDate
             GROUP BY Pozew_Id)
             ) + 1,0) THEN Kwota_PLN ELSE 0 END)) AS 'M0'
  FROM
  Dokumenty D
  JOIN Pozew P
      ON D.Pozew_Id = P.Pozew_Id
  WHERE
  ((P.NGp IS NOT NULL
  AND
  P.NGp &lt; 200)
  OR
  (P.NGp IS NULL
  AND
  P.NGz &lt; 200))
  AND
  P.Stan IN ('PZ-PORAŻKA','PZ-PORAŻ-UB','PZ-PSPPOR-SP')
  AND
  (SELECT TOP 1
       dbo.Dokumenty.Data_platnosci
   FROM
       dbo.Dokumenty
   WHERE
       dbo.Dokumenty.Pozew_Id = P.Pozew_Id
       AND
       dbo.Dokumenty.Nazwa_kosztu = 0x0000000000000008
   ORDER BY
       dbo.Dokumenty.Data_platnosci) &gt;= @fromDate
  </code></pre>