I need help using COUNT and DISTINCT in my SQL SERVER 2008 query

Hi Experts,
I need help with the following query.

I need to use count and distinct in my query (using a case statement).  I want my query to return the CASEID, SUM of the QTY,
and 'Mixed SKUs' when my case has more than 1 SKU # in it, or the SKU # if it only has 1 SKU.

Here's my current query
SELECT CASEID, SUM(QTY) AS [QTY],
CASE WHEN COUNT(DISTINCT [SKU]) <> 1 THEN 'Mixed SKUs' ELSE [SKU] END AS [UPC]
FROM PICK
WHERE CASEID = '0002618669'
GROUP BY CASEID,SKU

Open in new window


HERE is some sample data
SKUS IN ORDER # 1
CaseDI                  QTY            SKU      
0002618669            2            1818985
0002618669            3            1518786
0002618669            10            1118287

Here's what I want ORDER #1  to return
CaseDI                  QTY            SKU      
0002618669            15            Mixed SKUs


SKUS IN ORDER # 2
CaseDI                  QTY            SKU      
0002618888            2            1818985


Here's what I want ORDER #2  to return
CaseDI                  QTY            SKU      
0002618888            2            1818985


How can I do this?  Thank you very much in advance.
mrotor
mainrotorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
I piloted this with my own test tables, and it seems to work pretty good. See if you like this:
DECLARE @Pick TABLE (
    CaseID  varchar(10) NOT NULL,
    Qty     smallint    NOT NULL,
    SKU     varchar(10) NOT NULL )

INSERT INTO @Pick VALUES ('0002618669', 2, '1818985')
INSERT INTO @Pick VALUES ('0002618669', 3, '1518786')
INSERT INTO @Pick VALUES ('0002618669', 10, '1118287')
INSERT INTO @Pick VALUES ('0002618888', 2, '1818985')

SELECT p1.CaseID,
       SUM(p1.Qty) AS Qty,
       CASE
          WHEN SkuCnt = 0 THEN NULL
          WHEN SkuCnt = 1 THEN p2.SKU
          ELSE 'Mixed SKUs'
       END AS UPC
FROM   @Pick p1
CROSS APPLY (SELECT TOP 1 SKU FROM @Pick p2
             WHERE p2.CaseID = p1.CaseID) p2
LEFT JOIN (SELECT CaseID, COUNT(DISTINCT SKU) AS SkuCnt
           FROM @Pick
           GROUP BY CaseID
           HAVING COUNT(DISTINCT SKU) = 1) p3
ON     p3.CaseID = p1.CaseID
GROUP BY p1.CaseID, SkuCnt, p2.SKU

Open in new window

0
dsackerContract ERP Admin/ConsultantCommented:
The trick is having to reference the table two more times, once for the one and only SKU (the CROSS APPLY), and once for the COUNT of DISTINCT SKU values (the LEFT JOIN). That provides everything needed for the CASE statement decisions.

I used a LEFT JOIN, just in case you ever have NULL in the SKU field. If your SKU definition is NOT NULL, you can change that to an INNER JOIN.
0
mainrotorAuthor Commented:
dsacker,
can you provide a sample of the syntax?
0
mainrotorAuthor Commented:
Sorry,
I just noticed that you did provide a sample on your first post.  Thank you.
0
Scott PletcherSenior DBACommented:
mainrotor, your initial query was very close; here's an adjusted version that should efficiently give you what you need:

SELECT CASEID, SUM(QTY) AS [QTY],
    CASE WHEN COUNT(DISTINCT [SKU]) = 1 THEN MAX(SKU) ELSE 'Mixed SKUs' END AS [UPC]
FROM PICK
--WHERE CASEID = '0002618669'
GROUP BY CASEID
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.