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?
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.