oracle null value problem with group by

I have the query below:

SELECT EMAIL.MAILADDRESS, COUNT (DISTINCT CUSTOMER.CUSTOMER_ID) distinct_count
    FROM    (  SELECT CUSTOMER_ID,
                      TRUNC (MAX (UPDATE_DATE)) AS UPDATE_TARIHI,
                      TRUNC (MIN (INSERT_DATE)) AS DATA_ENTRY_DATE,
                      'YES' AS MAILADDRESS
                 FROM ELECTRONIC_ADDRESS
                WHERE ADDRESS_TYPE = 'ELECTRONIC'
             GROUP BY CUSTOMER_开发者_高级运维ID) EMAIL
         RIGHT OUTER JOIN
            CUSTOMER
         ON (EMAIL.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID)
GROUP BY EMAIL.MAILADDRESS

I am joining email(subquery-incoludes 'E' AS MAILADDRESS) and customer tables. Some customers do not have emailadress so I think that result should be like belowe clause:

MAILADDRESS        DISTINCT_COUNT
YES                      158945
NULL                      76345

But when I run this query, only first line is shown ( whose mail address is YES )

If I change EMAIL.MAILADDRESS clause to nvl(EMAIL.MAILADDRESS,'NO') in the selected columns and group by part, the query runs perfectly. But I could not solve the problem and I can not change the query because of that query is generated from Business Objects Universe.

We are writing this part:

   SELECT CUSTOMER_ID,
                  TRUNC (MAX (UPDATE_DATE)) AS UPDATE_TARIHI,
                  TRUNC (MIN (INSERT_DATE)) AS DATA_ENTRY_DATE,
                  'YES' AS MAILADDRESS
             FROM ELECTRONIC_ADDRESS
            WHERE ADDRESS_TYPE = 'ELECTRONIC'
         GROUP BY CUSTOMER_ID

and then construct a right join relationship between this part and customer table.

How can I solve the problem? And why Oracle is not showing null values?

Using the following SQL, I was unable to re-create your problem.

CREATE TABLE a1 (a NUMBER);

CREATE TABLE a2 (a NUMBER);

INSERT INTO a1
   SELECT     LEVEL
   FROM       DUAL
   CONNECT BY LEVEL <= 10;

INSERT INTO a2
   SELECT     LEVEL
   FROM       DUAL
   CONNECT BY LEVEL <= 20;

SELECT   a1.a, COUNT(DISTINCT a2.a)
FROM     a1 RIGHT OUTER JOIN a2 ON a1.a = a2.a
GROUP BY a1.a;

This suggests that the problem is in the data, not in the SQL. Is it possible that when a customer doesn't have an e-mail address that there is still a record in the ELECTRONIC_ADDRESS table, but some other column is null?

If that's the case, I would change your sub-query as follows (where "address" is the actual column containing the e-mail address):

   SELECT CUSTOMER_ID,
          TRUNC (MAX (UPDATE_DATE)) AS UPDATE_TARIHI,
          TRUNC (MIN (INSERT_DATE)) AS DATA_ENTRY_DATE,
          CASE WHEN ADDRESS IS NULL 
               THEN NULL 
               ELSE 'YES' 
               END AS MAILADDRESS
     FROM ELECTRONIC_ADDRESS
    WHERE ADDRESS_TYPE = 'ELECTRONIC'
 GROUP BY CUSTOMER_ID

You say you join this query

 select customer_id
       ,trunc(max(update_date)) as update_tarihi
       ,trunc(min(insert_date)) as data_entry_date
       ,'YES' as mailaddress
   from electronic_address
  where address_type = 'ELECTRONIC'
  group by customer_id

to the customer table. doesn't that mean that you will only get the records that have an email address (='YES') ?