CODEWITHSUNDEEP
sqloracle-database
viki
viki

Reputation: 43

To find total number of rows

I have a table like this

Table1
=======    

A                    B
8                    5
2                    9
null                 4
2                    5

How to find total number of rows from table1,note if any column value is null in a row then that row should be considered as 2 rows?

I have tried with count(*)*2 and nvl function it doesn't work

Upvotes: 0

Views: 129

Answers (2)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

COUNT() is rowbased.. you can tweak it using SUM() instead..

select sum(NVL2(a,NVL2(b,1,2),2)) FROM TABLE1

CASE as suggested by @Vignesh is the simplest and more readable !!

COUNT() can also done like this.. But NOT a optimal solution at all!

SELECT COUNT(1) FROM
(    
    SELECT NVL(a,NVL(b,1)) FROM TABLEA
    UNION ALL
    SELECT NVL(a,NVL(b,1)) FROM TABLEA 
        WHERE A OR B iS NULL
)

Upvotes: 2

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try this

SELECT SUM(CASE WHEN A IS NULL OR B IS NULL THEN 2 ELSE 1 END) AS CountVal
FROM TABLE1

Fiddle Demo

O/P:

COUNTVAL
--------
5

Upvotes: 4

Related Questions