SQL Count function returning inaccurate results

Question

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?

• LBCnGA: Patrick, thanks for the response, that answer works, I didn't realize that you could use the "-"COUNT function when querying in that manner. Yes, the code is a bit clunky for sure and needs to be optimized for use in my absence in the future.
• Lennart - Slava Ukraini: You can shorten your CASE statements by replacing THEN 1 ELSE NULL END) with THEN 1 END).
• LBCnGA: Lennart, thanks for the response, your answer did reduce the footprint of the query.

Answer 1

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

• LBCnGA: Thanks David, you are correct, in that I did not specify any additional filtering criteria for the users5 category as the requester is only concerned with the users1 - users4 filters. therefore, users5 filter should give count of entries that are remaining and do not match any of the filters specified for the users1 - users4 categories.

Answer 2

Here is a CTE (Common Table Expression) version of Pavel's answer. It avoids the procedural restrictions of many interfaces and can be used as a single query or a View.

WITH cte AS
(
 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 users_All
 FROM table_USERS 
 WHERE is_terminated = 'False'
)
SELECT users1, users2, users3, users4, users_ALL- (users1+ users2+ users3+ users4)
FROM cte
;

• LBCnGA: Thanks RBarry for the response, I tried the code sample you provided and am receiving the following response from the oracle db: ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected" *Cause: *Action: Error at Line: 4 Column: 14
• RBarryYoung: @LBCnGA Sorry, I never noticed before but apparently Oracle doesn't like the leading alias syntax even though it's valid SQL. I have posted an updated version that works on Oracle.
• ypercubeᵀᴹ: @RBarryYoung the "leading alias" syntax is proprietary (SQL Server only) syntax. Doesn't work anywhere else. And it's certainly not valid standard SQL.
• RBarryYoung: @ypercubeTM Hmm, it appears that you are correct. Thought I had looked this one up a couple of years ago, but I must have confused it with something else.
• Pavel Nefyodov: Thanks for posting it. I was away so could not reply. You have my vote up.

Answer 3

The part of the code

SELECT COUNT(*) AS USERS5 FROM table_USERS WHERE is_terminated = 'False'

returns the total number of users as expected.

You can keep the query and introduce parameters to account for the remaining (USER5) type of users i.e.

DECLARE @users1, @users2, @users3, @users4, @users5 int 
SELECT
@users1=COUNT(CASE WHEN unique_id like '%OU=Users%' and employee_type > '-1' THEN 1 ELSE NULL END)
,@users2=COUNT(CASE WHEN unique_id like '%OU=Vendors%' and employee_id is null and employee_type is null THEN 1 ELSE NULL END)
,@users3= COUNT(CASE WHEN unique_id like '%OU=Temp Users%' and employee_type ='-1' THEN 1 ELSE NULL END)
,@users4=COUNT(CASE WHEN unique_id like '%OU=Service%' THEN 1 ELSE NULL END)
FROM table_USERS WHERE is_terminated = 'False'
SELECT @users1, @users2, @users3, @users4, COUNT(*)- (@users1+ @users2+ @users3+ @users4)
FROM table_USERS WHERE is_terminated = 'False'

the above will work in t-SQL. Oracle SQL parameters are declared using ':' prefix followed by name of the parameter.

Alternatively try this clunky code:

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(*)-COUNT(CASE WHEN unique_id like '%OU=Users%' and employee_type > '-1' THEN 1 ELSE NULL
 END)-COUNT(CASE WHEN unique_id like '%OU=Vendors%' and employee_id is null and employee_type is null THEN 1 ELSE NULL
 END)-COUNT(CASE WHEN unique_id like '%OU=Temp Users%' and employee_type ='-1' THEN 1 ELSE NULL
 END)-COUNT(CASE WHEN unique_id like '%OU=Service%' THEN 1 ELSE NULL
 END)
 AS USERS5
FROM table_USERS 
WHERE is_terminated = 'False' 

• RBarryYoung: A CTE is probably preferable.
• LBCnGA: Thank you Pavel, unfortunately, the application which rides on-top of this oracle db does not allow for declarations without changing the parameters of the database and subsequent schema. Therefore, the solution provided cannot be implemented.
• RBarryYoung: I can add the CTE version to your answer if you want, this would fix the Declarations problem.
• LBCnGA: RBarry, I apologize I missed your response, please add the common table expression (CTE).
• RBarryYoung: Actually, I was offering to edit Pavel's post to add a CTE version of his answer. If he does not respond, then I will post a separate answer.