SQL*PLUS How to display a count of 0

Thread Tools Search this Thread
Top Forums Programming SQL*PLUS How to display a count of 0
# 1  
Old 01-21-2015
SQL*PLUS How to display a count of 0


I have been frantically googling and checking some sqlplus forums, but can't find the correct syntax.

Basically within sqlplus I want to do a count on a table and if the count is 0 it displays 0 instead of "no rows found".

For eample:

select count(*) from tableA where valueA='X';

Which I would like to return


if there are no rows on the table where valueA='X'

Any ideas?

# 2  
Old 01-21-2015
This looks good to me with an Oracle database.
SQL> set heading off
SQL> select count(*) from dba_tables where owner = 'RBATTE1' ;



I am logged on to the unix server and then a local sqlplus session, if that makes a difference. I'm not sure why it would though.

# 3  
Old 01-21-2015
OK, thanks. I've ended up asking the wrong question by trying to simplify it too much :0)

So my actual query is a bit more complicated and uses case and group by conditions.

So how can I make it display a count of 0 if no rows are found in this situation

e.g. (another simplified select not including the case)

select owner, table_name, count(*) from all_tables
where owner='ME'
group by owner, table_name;
no rows selected

# 4  
Old 01-21-2015
You can get a count of 0 by including 'ME' in the data set you select from:
select owner,table_name,count(table_name)
  from (select owner,table_name from all_tables
        union all
        select 'ME',null table_name from dual)
 where owner = 'ME'
 group by owner,table_name

ME                                                                            0

But be careful: count(*) counts the number of records. Because there now is a record with owner='ME' you'd get a result of 1. You get 0 because table_name is null in that record.

Last edited by cero; 01-21-2015 at 08:47 AM..
These 2 Users Gave Thanks to cero For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to find the count of IP addresses that belong to different subnets and display the count?

Hi, I have a file with a list of bunch of IP addresses from different VLAN's . I am trying to find the list the number of each vlan occurence in the output Here is how my file looks like So what I am trying... (2 Replies)
Discussion started by: new2prog
2 Replies

2. Shell Programming and Scripting

Sql query output count

Hi Team, below sql rerturn 20 records, the result set i am going to assign to one variable and it showing count is 1. and i don't use count() in sql query... based on count, i need to fail the script. No_of_step=`echo ${g_count} | wc -l` function gf_count() { g_count=`sqlplus -s... (8 Replies)
Discussion started by: bmk123
8 Replies

3. UNIX for Dummies Questions & Answers

Single UNIX command to display users and to count them

Hello everyone, I am new to Unix and I am stuck with a problem. I need only a single command to display the output of who and then add the total number of users and display at the bottom of that output. Example-: (Expected output) sreyan@debian:~$ <command> sreyan tty7 ... (7 Replies)
Discussion started by: sreyan32
7 Replies

4. Shell Programming and Scripting

Cannot get literal ampersand to display in SQL output

Gurus, Thanks so much for your help, in advance. I'm using ksh and outputting a literal string value to an output file, however, Unix isn't playing by SQL's rules. The ampersand character which I'm trying to disply as a knowledge base link is screwing up the output. Typically, the "&&" is... (1 Reply)
Discussion started by: WhoDatWhoDer
1 Replies

5. UNIX for Advanced & Expert Users

v$sql not display correct sql_text

Hi folks, I am facing one problem with v$sql, i need to store updating sql query in temp table when one trigger get fired on some update sql statement. but with "sql_text" , i am not getting correct update statement in temp table. I am getting sql_text with this cursor statement. select... (0 Replies)
Discussion started by: apskaushik
0 Replies

6. Shell Programming and Scripting

Read File and Display The Count of a particular field

Hi Mates, I require help in the following: I have the following file snmp.txt Wed Mar 2 16:02:39 SGT 2011 Class : mmTrapBladeS origin : hostname : msg : IBM Blade Alert: Calendar Index : Fri Mar 4 07:10:54 SGT 2011 Class : mmTrapBladeS... (2 Replies)
Discussion started by: dbashyam
2 Replies

7. Shell Programming and Scripting

How to display fields and values in sql+ for Oracle DB

Hello, I'm trying to do a select for an Oracle table but the output gives me only filelds values without fields name as in Informix. Is there anyway to display both in output ? For instance, the output will be : Name Rico Age 30 Position Engineer Thx, (5 Replies)
Discussion started by: rany1
5 Replies

8. Shell Programming and Scripting

How to display and count

Hi there, I'd like to find a way to display a string and count the words in it. supernova:~# echo 'hello world' | tee - | wc Unfortunately, this doesn't work. Any idea? Thanks in advance. Santiago (15 Replies)
Discussion started by: chebarbudo
15 Replies

9. Shell Programming and Scripting

Display the count of files

I am new to shell programming. Can anyone help me out with anyone of these? Display a count of the number of regular files, the number of symbolic links, the number of sub-directories, the number of block-special files, and the number of character-special files in the directory. I don't... (4 Replies)
Discussion started by: wayne1411
4 Replies
Login or Register to Ask a Question

Featured Tech Videos