CODEWITHSUNDEEP
sqlstatisticsdb2
GML-VS
GML-VS

Reputation: 1100

DB2 SQL column percentage calculation

Need to calculate a percentage of filled rows in a column(string).

String column could contain zero length strings (should be excluded)

How to re-write this SQL in one sentence (without WITH operator)?

          with A(COUNT)  // needed rows
             as(
                 select count(FAMILY) from T1 
                    where length(FAMILY)>0
               ),
               B(COUNT) // total rows
             as(
                 select count(*) from T1)

         select A.COUNT*100/B.COUNT from A,B

Upvotes: 0

Views: 1293

Answers (1)

Derek Williams
Derek Williams

Reputation: 229

You can use sub-selects instead of WITH; for example:

select
  ((select count(*) from T1 where length(FAMILY) > 0) * 100) /
  (select count(*) from T1)
from sysibm.sysdummy1

Upvotes: 1

Related Questions