Title: oracle null value problem with group by
Tags: sql;oracle
Question: 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?


Here is another answer: 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

Here is another answer: 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') ?
Comment for this answer: If you look at the first query in the post, you'll see that this portion is on the left side of the `right outer join`.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2011:00:07/27/11', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sql><oracle>', 'Title': 'oracle null value problem with group by', 'ViewCount': 2916, 'id': 6844938, 'user_AboutMe': None, 'user_AccountId': 789917.0, 'user_DisplayName': 'jerfi', 'user_Reputation': 1.0, 'user_Views': 1.0, 'user_id': 865456.0}}