?
Solved

I need help!  I get an aggregate error with my SQL Query

Posted on 2014-08-19
2
Medium Priority
?
271 Views
Last Modified: 2014-08-20
Hi Experts,
I need your help with my query.  I get the following aggregate error with the below SQL Query

Error I get:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

My Current query:
SELECT  S.SKU, S.ALTSKU AS UPC, 
     CASE WHEN IC.Available < 1 THEN 
          CASE IC.Category WHEN 'Tops' THEN 
               CASE WHEN (ISNULL(FLOOR(SUM(SKX.QTY - SKX.QTYALLOCATED - 
               (SELECT SUM(ARTA.quantity) FROM OrdDetail AS ARTA LEFT JOIN OrdHeader AS ARTB ON ARTA.Ordernumber = ARTB.Ordernumber WHERE ARTA.itemnmbr = S.SKU)
               ) * IC.Available), 0)) < 15 THEN 0 ELSE 
               ISNULL(FLOOR(SUM(SKX.QTY - SKX.QTYALLOCATED - 
               (SELECT SUM(ARTA.quantity) FROM OrdDetail AS ARTA LEFT JOIN OrdHeader AS ARTB ON ARTA.Ordernumber = ARTB.Ordernumber WHERE ARTA.itemnmbr = S.SKU)
               ) * IC.Available), 0) 
               END
          END
     END AS SKU_QTY
FROM   Cur_Inventory AS R INNER JOIN
       Inventory_Cat AS IC ON R.SKU = IC.SKU INNER JOIN
       SKU AS S ON IC.SKU = S.SKU LEFT OUTER JOIN
       LOT AS SKX ON S.SKU = SKX.SKU
GROUP BY S.SKU, S.ALTSKU, IC.Category, IC.Available
ORDER BY S.SKU

Open in new window

0
Comment
Question by:mainrotor
2 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 40270689
Something like this perhaps:
SELECT  S.SKU,
        S.ALTSKU AS UPC,
        CASE WHEN IC.Available < 1
             THEN CASE IC.Category
                    WHEN 'Tops' THEN CASE WHEN (ISNULL(FLOOR(SUM(SKX.QTY - SKX.QTYALLOCATED - ISNULL(o.TotalQuantity, 0)) * IC.Available), 0)) < 15 THEN 0
                                          ELSE ISNULL(FLOOR(SUM(SKX.QTY - SKX.QTYALLOCATED - ISNULL(o.TotalQuantity, 0)) * IC.Available), 0)
                                     END
                  END
        END AS SKU_QTY
FROM    Cur_Inventory AS R
        INNER JOIN Inventory_Cat AS IC ON R.SKU = IC.SKU
        INNER JOIN SKU AS S ON IC.SKU = S.SKU
        LEFT OUTER JOIN LOT AS SKX ON S.SKU = SKX.SKU
        LEFT JOIN (
                   SELECT   ARTA.itemnmbr,
                            SUM(ARTA.quantity) TotalQuantity
                   FROM     OrdDetail AS ARTA
                            LEFT JOIN OrdHeader AS ARTB ON ARTA.Ordernumber = ARTB.Ordernumber
                   GROUP BY ARTA.itemnmbr
                  ) o ON o.itemnmbr = S.SKU
GROUP BY S.SKU,
        S.ALTSKU,
        IC.Category,
        IC.Available
ORDER BY S.SKU

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 40272516
It's a little difficult to tell what you're trying to accomplish but, typically, using case with aggregrate functions would be more like -
sum(case when ... then something else someotherthing ...
rather than case when then sum(case when ...
Perhaps you can post some sample relevant data and your desired results to help clarify your intent.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question