Aggregate Function , Distinct and NULL Values in Oracle.

While working on SQL Server with queries having aggregation got a warning message.
“Warning: Null value is eliminated by an aggregate or other SET operation.”

So thought let write up something on Aggregation and NULL Values for Tradition RDBMS for basic understanding.
Aggregation is key operations for any analytics or summarize information.
Our traditional RDBMS has NULL to mark any information Undefined or Unknown.

Both Aggregation and NULL are related in a way for different standard aggregation method oracle provides (MIN/MAX/COUNT/AVG/SUM).
Lets walk through a Query example and will discuss more on same.

WITH DATA1 AS
(
SELECT 1 COL1 FROM DUAL UNION ALL
SELECT NULL COL1 FROM DUAL UNION ALL
SELECT 1 COL1 FROM DUAL UNION ALL
SELECT 1 COL1 FROM DUAL UNION ALL
SELECT NULL COL1 FROM DUAL UNION ALL
SELECT 2 COL1 FROM DUAL
)
SELECT 'NON - DISTINCT' AS INFO, COUNT(*) "CNT_*", COUNT(1) CNT_ROW, COUNT(COL1) CNT_COLUMN , COUNT(DISTINCT COL1) CNT_DISTINCT,
COUNT(NVL2(COL1, NULL ,1)) CNT_NULL,
AVG(COL1) AVG, SUM(COL1) SUM, MIN(COL1) MIN , MAX(COL1) MAX FROM DATA1
UNION ALL
SELECT 'DISTINCT' AS INFO, COUNT(*), COUNT(DISTINCT 1) CNT_ROW, COUNT(COL1) CNT_COLUMN , COUNT(DISTINCT COL1) CNT_DISTINCT,
COUNT(NVL2(COL1, NULL ,1)) CNT_NULL,
AVG(DISTINCT COL1) AVG, SUM(DISTINCT COL1) SUM, MIN(DISTINCT COL1) MIN , MAX(DISTINCT COL1) MAX FROM DATA1
;

We have total 6 Rows, with 2 as NULL and 2 distinct values.
Let see output for different aggregation functions and understand it better.

Output ::
aggregate

Key Findings:
1. Count(*) and Count(1) (Or any constant expression like count(‘a’) count(23123)) are similar and are use to count total rows return by a SQL.
COUNT function without any table column as input expression are use to count total number of rows return by query.

2. Count (Column) and Count(Distinct Column)
Will Ignore NULL Values for counting.Output values will be based on count of all Non Null-able values within that column.

3. Count(Distinct *)
Distinct * is not allowed, it would give an Exception.
Distinct within aggregation only works for any constant or column expression.

4. Count(NULL) will always be 0.
Expression as NULL can’t be evaluated as it is always Not Defined and hence output for same would be always Zero.

WITH DATA1 AS
(
SELECT 1 COL1 FROM DUAL UNION ALL
SELECT NULL COL1 FROM DUAL UNION ALL
SELECT 1 COL1 FROM DUAL UNION ALL
SELECT 1 COL1 FROM DUAL UNION ALL
SELECT NULL COL1 FROM DUAL UNION ALL
SELECT 2 COL1 FROM DUAL
)
SELECT COUNT(NULL) FROM DATA1;
output :
COUNT(NULL)
-----------
0

5.COUNT(DISTINCT 1)
Distinct on any constant expression will always give output as 1, as we don’t have any scope for non distinct values.

6.AVG/SUM/MIN/MAX will ignore NULL values for any evaluations.
In Avg while consideration of denominator all NULL values are ignore.
So basically it is
AVG ( (SUM of all Non NULL Column data)/(Count of Column values) i.e. AVG(SUM(COL1)/COUNT(COL1))

7. AVG(DISTINCT COL1) and AVG(COL1)
When we add Distinct with column expression for AVG function, it turn out to be
AVG ( (SUM of all non null and distinct column data) / (distinct count) i.e. AVG(SUM(DISTINCT COL1)/COUNT(DISTINCT COL1))

Distinct is impose for both SUM and Count of denominator.

8. How to Count NULL for a column.
We can use below expression to count total number of nulls for a column.
COUNT(NVL2(COL1, NULL ,1))

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s