SQL Count function returning inaccurate results

countoracleselect

I am trying to write a query in oracle to list the number of records contained in a single table filtered by multiple columns in the tablespace. Here is the query syntax I have come up with:

SELECT COUNT(CASE WHEN unique_id like '%OU=Users%' and employee_type > '-1' THEN 1 ELSE NULL
         END) AS USERS1
   ,COUNT(CASE WHEN unique_id like '%OU=Vendors%' and employee_id is null and employee_type is null THEN 1 ELSE NULL
          END) AS USERS2
   ,COUNT(CASE WHEN unique_id like '%OU=Temp Users%' and employee_type ='-1' THEN 1 ELSE NULL
          END) AS USERS3
   ,COUNT(CASE WHEN unique_id like '%OU=Service%' THEN 1 ELSE NULL
          END) AS USERS4
   ,COUNT(*) AS USERS5
FROM table_USERS 
WHERE is_terminated = 'False'

Here is the resulting output:

users1=1192
users2=38
users3=25
users4=240
users5=1548

The result is not correct it is showing "users5" as the total number of users that exist in the database (1548), when it should show me only 53 users which don't match any of the user categories (users1 – users4).

What am I missing and is there a more simplistic way to write this query?

Best Answer

It looks like you just have "COUNT(*) AS USERS5", I'm not seeing where you're filtering out the other types of user categories.

Related Solutions

DB2 Count problem

Use fetch first 20001, and log an error if you get the last record.

Postgresql – Determine percentage from count() without cast issues

SELECT (count(refinst) * 100)::numeric / NULLIF(count(*), 0) AS refinst_pct
    -- count(refinst) * 100.0 / NULLIF(count(*), 0) AS refinst_pct  -- simpler
FROM   patients;

Do not use a subselect. Both aggregates can be derived from the same query. Cheaper.
Also, this is not a case for window functions, since you want to compute a single result, and not one result per row.
Cast to any numeric type that supports fractional digits, like @a_horse already explained.
Since you want to round() to two fractional digits I suggest numeric (which is the same as decimal in Postgres).
It's enough to cast one value involved in a calculation, preferably the first. Postgres automatically settles for the type that does not lose information.
Or, simpler yet: since we multiply anyway, use a numeric constant that's coerced to numeric automatically because of the decimal point (100.0).
It's generally a good idea to multiply before you divide. This typically minimizes rounding errors and is cheaper.
In this case, the first multiplication (count(refinst) * 100) can be computed with cheap and exact integer arithmetic. Only then we cast to numeric and divide by the next integer (which we do not cast additionally).
NULLIF(count(*), 0) prevents division by zero (raising an exception). We get NULL as (unknown) percentage if there are no rows at all.

Rounded to two fractional digits:

SELECT round((count(refinst) * 100)::numeric / NULLIF(count(*), 0), 2) AS refinst_pct
FROM   patients;

Related Question