Conditional Counting In SQL


If you ever want to conditionally count the number of times a particular condition occurs in SQL, you can do it in Oracle using the case and count functions. Here's a simple example which counts the number of males/females stored in PS_PERSONAL_DATA.

select
    count(case when SEX = 'M' then 1 end) as MALES,
    count(case when SEX = 'F' then 1 end) as FEMALES
from PS_PERSONAL_DATA

All that is happening is that the case statement returns a 1 for every instance where the gender is M or F and the count, counts each returned value of 1 giving a summary like this:

MALES FEMALES
10004 20421

The conditions in the case statement can be a lot more complex and can include sub-queries.


Revision #1
Created Tue, Jul 23, 2019 7:21 PM by PeopleSoft Wiki
Updated Tue, Jul 23, 2019 7:23 PM by PeopleSoft Wiki