CODEWITHSUNDEEP
sql-servercountdistinct
gpsrosak
gpsrosak

Reputation: 107

COUNT(DISTINCT()) Return false value

I try to count total employee in my subquery table. Suppose the count result will return 0, but it keeps returning 1.

If I try to return only employee_id and month together, I didn't get any return value for may which is correct, but each time I try to count(distinct), I will get 1 as my return value. This is my sql

SELECT

  count (distinct(CASE WHEN (x.month =5 and x.employee_id <> 0) THEN 
  x.employee_id
        ELSE 0 END)) as test_may
FROM(
 (
SELECT 

     h.month,
     h.employee_id,
     eb.employee_no,
     ee.company_code,
     h.amount,   
     h.year, 
     h.trx_type,   
     h.trx_code, 
     v.trx_desc,
    h.frequency,
    h.run_sequence

FROM 
    v_employee h,
    v_trans v,
    employee_emp ee, 
    employee eb          
  WHERE
        ( h.year = 2014 ) AND  
        ( h.employee_id = ee.employee_id ) AND  
        ( ee.employee_id = eb.employee_id ) AND 
        ( h.employee_no = eb.employee_no ) AND 
        ( h.trx_code = v.trx_code ) AND
        ( h.trx_type = v.trx_type ) AND
        ( v.company_code = ee.company_code OR v.company_code is NULL) AND
        ( h.trx_type IN ('S','B','N','O','A','D','L') )
)
)x,
employee_emp ee,
employee eb

 WHERE

    ( x.employee_id = ee.employee_id ) AND
    ( ee.employee_id = eb.employee_id ) AND
    ( x.employee_no = eb.employee_no ) AND
     ( x.year = 2014 )

Upvotes: 1

Views: 638

Answers (2)

trincot
trincot

Reputation: 350147

The count as you have it now will also count the 0 that is in the ELSE clause of the CASE expression. Even with DISTINCT still one instance of that 0 will be counted.

Remove the ELSE 0 so that you have NULL -- which is not counted:

count (distinct(CASE WHEN x.month =5 and x.employee_id <> 0
                     THEN x.employee_id
                END)) as test_may

Note that with NULLIF you can shorten this expression to:

count (distinct(CASE x.month WHEN 5 THEN NULLIF(x.employee_id, 0) END)) as test_may

Upvotes: 2

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Your count will return the same count even in both cases. Because you are giving the value for Count function in both the cases.

Change from

 count (distinct(CASE WHEN (x.month =5 and x.employee_id <> 0) THEN 
  x.employee_id
        ELSE 0 END))

To

 count (distinct(CASE WHEN (x.month =5 and x.employee_id <> 0) THEN 
  x.employee_id
        ELSE NULL END))

Count will just count the values whether it is 0 or 100 as 1 & skip null values while counting. So in the Else condition NULL will give you correct output.

Upvotes: 1

Related Questions