Donald K. Burleson Oracle Tips

Counting column values within a table

A very common coding request is the desire to count the number of distinct values in a low cardinality column.  For example, a motor vehicle database may need to know the number of blue cars in Ohio.  These queries are easily solved by using the Oracle decode built-in function (BIF).

In the code below, we see that we use the sum and decode functions together.

select

   dept,

   sum(  decode(sex,'M',1,0)) MALE,

   sum(  decode(sex,'F',1,0)) FEMALE,

   count(decode(sex,'M',1,'F',1)) TOTAL

from  

   emp

group  by

   dept

;

Here is the output.  Note how the decode function translates the values for Male “M” into a binary 0 or 1.  That, in turn, allows the SQL to perform the counts of the values.  Here is a sample of the output.

SQL> @count_sex

MALE      FEMALE        TOTAL

--------  ------------  ------------

  34         42            76

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: