0

I have got a SALES table that has 4 document types, and they are created with reference to each other in the following order:

Offer -> Order -> Delivery -> Invoice.

For each new row their REFDOCTYPE and REFDOCNUM column is updated according to the previous document type and number. I want counts of offers with different sub-queries grouped by customers.

  1. First column counts Offers that has become an invoice in the end.
  2. Second column counts offers that have a sales order but didn't end up being an invoice.
  3. Last column counts offers that are passive (no document referenced them).

This query gives the following error 3 times:

"Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

How can I write it differently ? I tried making them a left join but it didn't work. Here is the query:

    SELECT 
Q.CUSTOMER,
SUM(CASE WHEN EXISTS
(SELECT COMPANY FROM SALES O WITH (NOLOCK) WHERE O.COMPANY = Q.COMPANY AND O.REFDOCTYPE = Q.DOCTYPE AND O.REFDOCNUM = Q.DOCNUM AND O.DOCTYPE = 'ORDER' AND O.ISDELETED = 0 AND EXISTS
 (SELECT COMPANY FROM SALES D WITH (NOLOCK) WHERE AND D.COMPANY = O.COMPANY AND D.REFDOCTYPE = O.DOCTYPE AND D.REFDOCNUM = O.DOCNUM AND D.DOCTYPE = 'DELIVERY' AND D.ISDELETED = 0 AND EXISTS
  (SELECT COMPANY FROM SALES I WITH (NOLOCK) WHERE AND I.COMPANY = D.COMPANY AND I.REFDOCTYPE = D.DOCTYPE AND I.REFDOCNUM = D.DOCNUM AND I.DOCTYPE = 'INVOICE' AND D.ISDELETED = 0))) THEN 1 ELSE 0 END) AS HasInvoice,

  SUM (CASE WHEN EXISTS
(SELECT COMPANY FROM SALES O WITH (NOLOCK) WHERE O.COMPANY = Q.COMPANY AND O.REFDOCTYPE = Q.DOCTYPE AND O.REFDOCNUM = Q.DOCNUM AND O.DOCTYPE = 'ORDER' AND O.ISDELETED = 0 AND NOT EXISTS
 (SELECT COMPANY FROM SALES D WITH (NOLOCK) WHERE AND D.COMPANY = O.COMPANY AND D.REFDOCTYPE = O.DOCTYPE AND D.REFDOCNUM = O.DOCNUM AND D.DOCTYPE = 'DELIVERY' AND D.ISDELETED = 0)) THEN 1 ELSE 0 END) AS OrderWithoutInvoice,

   SUM (CASE WHEN NOT EXISTS
(SELECT COMPANY FROM SALES O WITH (NOLOCK) WHERE O.COMPANY = Q.COMPANY AND O.REFDOCTYPE = Q.DOCTYPE AND O.REFDOCNUM = Q.DOCNUM AND O.DOCTYPE = 'ORDER' AND O.ISDELETED = 0) THEN 1 ELSE 0 END) AS NoOrder

FROM SALES Q WITH (NOLOCK)
WHERE Q.DOCTYPE = 'OFFER'
  AND Q.ISDELETED = 0
  AND Q.DOCDATE > '2019-09-01'
  GROUP BY Q.CUSTOMER
0

As the error says, that is limitation. However, you probably want try as follows:

SELECT  Q.CUSTOMER,
        COUNT (DISTINCT CASE WHEN O.REFDOCNUM is not null and D.REFDOCNUM is not null and D.REFDOCNUM is not null
                    THEN S.DOCNUM END
              ) AS HasInvoice,

        COUNT (DISTINCT CASE WHEN O.REFDOCNUM is not null and D.REFDOCNUM is null
                    THEN S.DOCNUM END
              ) AS OrderWithoutInvoice

        COUNT (Distinct S.DOCNUM) as TotalOffers

FROM SALES S WITH (NOLOCK)
    LEFT JOIN 
        (SELECT * 
         FROM   SALES O WITH (NOLOCK) 
         WHERE  O.DOCTYPE = 'ORDER' AND O.ISDELETED = 0 
         ) AS O ON S.COMPANY = O.COMPANY AND S.REFDOCTYPE = O.DOCTYPE AND S.DOCNUM = O.REFDOCNUM

    LEFT JOIN 
        (SELECT * 
         FROM   SALES WITH (NOLOCK) 
         WHERE  DOCTYPE = 'INVOICE' AND ISDELETED = 0 
         ) AS I ON S.COMPANY = I.COMPANY AND S.REFDOCTYPE = I.DOCTYPE AND S.DOCNUM = I.REFDOCNUM

    LEFT JOIN 
        (SELECT * 
         FROM   SALES WITH (NOLOCK) 
         WHERE  DOCTYPE = 'DELIVERY' AND ISDELETED = 0 
         ) AS D ON S.COMPANY = D.COMPANY AND S.REFDOCTYPE = D.DOCTYPE AND S.DOCNUM = D.REFDOCNUM

WHERE Q.DOCTYPE = 'OFFER' AND Q.ISDELETED = 0 AND Q.DOCDATE > '2019-09-01'
GROUP BY Q.CUSTOMER

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.