Oracle: analytical function divided by nullable values
I do NOT want to read NULL values. Because NULL shouldn't equal NULL.
Look at this query result: link
WITH temp as (
SELECT 'A' as master , 1 Col from dual
UNION SELECT 'A' , 3 from dual
UNION SELECT 'B' , 1 from dual
UNION SELECT 'B' , 2 from dual
UNION SELECT 'C' , 1 from dual
UNION SELECT NULL , 1 from dual
UNION SELECT NULL , 2 from dual)
SELECT
master,
count(Col) over (partition by master)
FROM
temp
+3
source to share
3 answers
If you don't want to filter out rows where master
IS is NULL, you can do something like
SELECT master, SUM (CASE WHEN master IS NULL THEN 0 ELSE 1 END) OVER (PARTITION by master) FROM temp
SQL> ed
Wrote file afiedt.buf
1 WITH temp as (
2 SELECT 'A' as master , 1 Col from dual
3 UNION SELECT 'A' , 3 from dual
4 UNION SELECT 'B' , 1 from dual
5 UNION SELECT 'B' , 2 from dual
6 UNION SELECT 'C' , 1 from dual
7 UNION SELECT NULL , 1 from dual
8 UNION SELECT NULL , 2 from dual)
9 SELECT master,
10 SUM( CASE WHEN master IS NULL
11 THEN 0
12 ELSE 1
13 END) OVER (PARTITION BY master)
14* FROM temp
SQL> /
M SUM(CASEWHENMASTERISNULLTHEN0ELSE1END)OVER(PARTITIONBYMASTER)
- -------------------------------------------------------------
A 2
A 2
B 2
B 2
C 1
0
0
+3
source to share
Alternatively, filter them out:
1 WITH temp as (
2 SELECT 'A' as master , 1 Col from dual
3 UNION SELECT 'A' , 3 from dual
4 UNION SELECT 'B' , 1 from dual
5 UNION SELECT 'B' , 2 from dual
6 UNION SELECT 'C' , 1 from dual
7 UNION SELECT NULL , 1 from dual
8 UNION SELECT NULL , 2 from dual)
9 SELECT
10 master,
11 count(Col) over (partition by master)
12 FROM
13 temp
14* WHERE master is not null
SQL> /
M COUNT(COL)OVER(PARTITIONBYMASTER)
- ---------------------------------
A 2
A 2
B 2
B 2
C 1
+4
source to share
Null is nonzero. But in terms of aggregation, zero is equivalent to null. It is not the analyst who does this; it is the aggregation itself.
1 WITH temp as (
2 SELECT 'A' as master , 1 Col from dual
3 UNION SELECT 'A' , 3 from dual
4 UNION SELECT 'B' , 1 from dual
5 UNION SELECT 'B' , 2 from dual
6 UNION SELECT 'C' , 1 from dual
7 UNION SELECT NULL , 1 from dual
8 UNION SELECT NULL , 2 from dual )
9 SELECT
10 master,
11 count(Col)
12 FROM
13* temp group by master
SQL> /
M COUNT(COL)
- ----------
2
A 2
B 2
C 1
+3
source to share