Cheesy Code

Generate a sequence of numbers with a NULL to test:

WITH seq AS
(
    SELECT num
    FROM   (
            SELECT     LEVEL num
            FROM       dual
            CONNECT BY LEVEL <= 9 + 1
            )
    UNION
    SELECT null
    FROM dual
)
SELECT * FROM seq;

Let's try a few aggregate functions:

WITH seq AS
(
    SELECT num
    FROM   (
            SELECT     LEVEL num
            FROM       dual
            CONNECT BY LEVEL <= 9 + 1
            )
    UNION
    SELECT null
    FROM dual
)
SELECT COUNT(*), COUNT(num), SUM(num), AVG(num)
  FROM seq;

So we can see that COUNT(*) counts the number of rows returned, whereas COUNT, SUM and AVG of num all ignore the NULL. Having the aggregates ignore NULLs makes possible the kind of query where you are classifying and aggregating different values in a single column. I'll show you what I mean by first creating some random data which can be classified:

EXEC dbms_random.initialize(123);

WITH seq AS
(
    SELECT TRUNC(dbms_random.value(1,4)) rnd, num
    FROM   (
            SELECT     LEVEL num
            FROM       dual
            CONNECT BY LEVEL <= 9 + 1
            )
    UNION
    SELECT null, null
    FROM dual
)
SELECT * FROM seq;

Now let's say we want the average of the num column for each of the posible values 1,2,3 in the rnd column. Since aggregates ignore NULLs we can easily do this with some CASE statements where we return NULL for things that aren't interesting confident in the knowledge that all aggregate functions will ignore those values:

EXEC dbms_random.initialize(123);

WITH seq AS
(
    SELECT TRUNC(dbms_random.value(1,4)) rnd, num
    FROM   (
            SELECT     LEVEL num
            FROM       dual
            CONNECT BY LEVEL <= 9 + 1
            )
    UNION
    SELECT null, null
    FROM dual
)
SELECT AVG(CASE WHEN rnd = 1 THEN num ELSE null END) one,
       AVG(CASE WHEN rnd = 2 THEN num ELSE null END) two,
       AVG(CASE WHEN rnd = 3 THEN num ELSE null END) three
  FROM seq;

Note that the dbms_random.initialize call is made to seed the random number generator to force the same sequence for each sample. Not sure if this sequence is reliable across instances or versions. This sequence was generated on 9i.

SELECT *
  FROM product_component_version;