asked on

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

dsacker

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

dsacker

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.

mainrotor

ASKER

dsacker,
can you provide a sample of the syntax?

mainrotor

ASKER

Sorry,
I just noticed that you did provide a sample on your first post. Thank you.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial