combine results from multiple sql results

[awalt]

Hello. thanks in advance for reading.

Don't do much sql so I need help with a combining the results from multiple sql statements into a single line.

Situation:
Want to return in one line how many times each column is "not null" in a table.

Table Definition:
SQL> create table xxx
2 (col1 varchar(5),
3 col2 varchar(5),
4 col3 varchar(5),
5 col4 varchar(5));

Data in table XXX:
COL1 COL2 COL3 COL4
----- ----- ----- -----
test test test test
test test test
test test test
test
test
test test test

Desired output:
Col1 Col2 Col3 Col4
---- ---- ---- ----
6 4 4 1

I can get the results for just one of the columns -- "select count(*) from XXX where col1 is not null;"

But how do I get the results for all 4 columns and get my desired output in 1 single sql statement.

Thanks!

[LKBrwn_DBA]

Try this:

select
 sum(decode(COL1,null,0,1)) COL1
,sum(decode(COL2,null,0,1)) COL2
,sum(decode(COL3,null,0,1)) COL3
,sum(decode(COL4,null,0,1)) COL4
from XXX;

[tamilselvan]

select
   ( select count(1) from tableA where col1 is not null) COL1,
   ( select count(1) from tableA where col2 is not null) COL2,
   ( select count(1) from tableA where col3 is not null) COL3,
   ( select count(1) from tableA where col4 is not null) COL4
from dual;

Tamil

[jmodic]

Hm, how about using the very basic SQL stuff, without any fancy "tricks"?

select
  count(col1),
  count(col2),
  count(col3),
  count(col4)
from my_table;

Cane it get any easier than that? Can it get any faster than that?