CODEWITHSUNDEEP
sqlsumconditional-statements
user3682096
user3682096

Reputation: 25

Conditional count using CASE WHEN

I have table like below 

CID ITEM    FLAG
C_1 ITEM_1  0
C_1 ITEM_2  1
C_1 ITEM_2  1
C_1 ITEM_2  0
C_2 ITEM_2  0
C_3 ITEM_2  1
C_3 ITEM_2  1
C_3 ITEM_3  1

and I want to produce table like

CID UNIQ_CNT_OF_CID
ITEM_1  0
ITEM_2  2
ITEM_3  1

The catch is - I want to count CID once for same CID.

I tried ,

SELECT  ITEM, CID,
      case when FLAG =1 then count(distinct CID)  else 0 end  as COUNTER 
      from T  
      group by ITEM

Please help !!

Upvotes: 1

Views: 2193

Answers (2)

Balah
Balah

Reputation: 2540

You were close. 2 items to point out:

  1. The group by must contain the same fields in the select (that are not in an aggregate function). since there is no need to group by CID, that should be removed from the select.
  2. You can use CASE for fields not in a group by, but inside the aggregate function.

So amending your SQL will now look like this:

SELECT ITEM,
    -- The CID is used inside the case, inside the count function.
    -- NULL will not be included in the count
    COUNT(DISTINCT CASE WHEN FLAG = 1 THEN CID ELSE NULL END) AS COUNTER 
    from T 
    group by ITEM

Result:

Result

Upvotes: 3

Nick
Nick

Reputation: 7451

I think I understand your question correctly, if not let me know.

You can use a windowed function to accomplish this:

SELECT
ITEM,
COUNT(DISTINCT CID) OVER (PARTITION BY ITEM) AS UNIQ_CNT_OF_CID
FROM T

This will give you the same number of rows, so you if you want the unique from this, you can use a CTE or sub-query it like so:

SELECT DISTINCT
ITEM, UNIQ_CNT_OF_CID
FROM
    (
    SELECT
    ITEM,
    COUNT(DISTINCT CID) OVER (PARTITION BY ITEM) AS UNIQ_CNT_OF_CID
    FROM T
    ) final

Upvotes: 2

Related Questions