CODEWITHSUNDEEP
sqloracle-databasecountduplicates
Jordan Holmer
Jordan Holmer

Reputation: 75

SQL How to count one column when another column is distinct

I have a table that looks something like this:

fin_aid_status | student_number
---------------|---------------
YES            | 111222
YES            | 111222
               | 111333
YES            | 111444

I want to count the number of fin_aid_status but not double count rows where student_number is duplicated. So the result I would like from this table would be 2. Not 3 because 111222 is in the table twice. There are many other columns in the table as well though so just looking for unique values in the table will not work.

EDIT: This is Oracle.

For example I am using code like this already:

select count(*), count(distinct student_number) from table

So for third column I would want to count the number on financial aid with unique student numbers.

So my expected output would be:

count(*) | count(distinct student_number) | count_fin_aid
4        | 3                              | 2

Upvotes: 0

Views: 6505

Answers (3)

xQbert
xQbert

Reputation: 35323

Use a case statement to evaluate the student_number when the fin_aid_status is not null; then count the distinct values.

SELECT count(Distinct case when fin_aid_status is not null 
                           then student_number end)  as Distinct_Student
FROM tbl;

Result using sample data: 2

Given Oracle:

With cte (fin_aid_status, student_number) as (
SELECT 'YES'            , 111222 from dual union all
SELECT 'YES'            , 111222 from dual union all
SELECT ''               , 111333 from dual union all
SELECT 'YES'            , 111444 from dual )
SELECT count(Distinct case when fin_aid_status is not null 
                           then student_number end) as DistinctStudentCnt 
FROM  cte;

Upvotes: 1

dbajtr
dbajtr

Reputation: 2044

I'm assuming here, add some expected results but:

SELECT fin_aid_status,
       COUNT(DISTINCT student_number)
FROM tablename
GROUP BY fin_aid_status;

Will give you count of distinct values in the student_number column for each value in the fin_aid_status column

Upvotes: 0

mrateb
mrateb

Reputation: 2499

If you are using MySQL you can write something as follows, if all you want is count 

SELECT count(DISTINCT student_number) FROM your_table WHERE fin_aid_status = 'YES';

Upvotes: 1

Related Questions