CODEWITHSUNDEEP
sql
Bob Francis
Bob Francis

Reputation: 47

Select Count Query Giving Inaccurate Results

I'm trying to query the table CANCELLATION_DEFINITION, and count the amount of rows that have an ACTION_TYPE value that isn't "-". Unfortunately, the query is giving me inaccurate results. For example, it returns 3 for this table when there are 6 rows in the table that have an ACTION_TYPE value other than "-". Code is below.

SELECT COUNT(*) 
FROM    (
     SELECT DISTINCT 
     ACTION_TYPE
     FROM CANCELLATION_DEFINITION WHERE ACTION_TYPE != '-'
     )AS distinctified

Upvotes: 1

Views: 132

Answers (4)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Try removing the distinct keyword and changed your query to:-

SELECT COUNT(ACTION_TYPE)
FROM CANCELLATION_DEFINITION WHERE ACTION_TYPE != '-';

Upvotes: 1

Benjamin Karlog
Benjamin Karlog

Reputation: 340

Try:

SELECT COUNT(*) 
FROM    (
     SELECT ACTION_TYPE
     FROM CANCELLATION_DEFINITION WHERE ACTION_TYPE != '-'
     )AS distinctified

Hope that helps

Upvotes: 2

Leopold Stotch
Leopold Stotch

Reputation: 1522

Try this below, I don't believe there is a need for your sub query:

SELECT COUNT(ACTION_TYPE) AS distinctified
FROM 
CANCELLATION_DEFINITION
WHERE ACTION_TYPE != "-"

Upvotes: 0

C Bauer
C Bauer

Reputation: 5103

When you perform a select distinct, it only returns distinct (ie different) values. So if you have action_type ["INSERT", "UPDATE", "UPDATE", "DELETE"] it will only give you 3 results because it merges UPDATE and UPDATE.

What you really want is

SELECT COUNT(ACTION_TYPE) 
FROM CANCELLATION_DEFINITION 
WHERE ACTION_TYPE != '-'

Upvotes: 3

Related Questions