CODEWITHSUNDEEP
sqloracle-databasesqlplus
guarrana
guarrana

Reputation: 79

How to not return a value of 0 when data does not exist in table, SQLPLUS

I have two tables, table A and B, and I am counting the number of occurences of a data type from table A,

    ENAME       SALES
    ---------- ----------
    Wilson          0
    Fox             1
    Wang            2
    Green           1
    Fox             0
    Phillips        2
    Loftus          0
    Smith           2
    Smith           3
    Wolf            2
    Jones           1
    Liu             0

Where when the sales count is 0, the ename does not exist in table B at all.

How would I go about removing the 0 count? I'm trying to get the return to look like this:

    ENAME       SALES
    ---------- ----------
    Wilson          
    Fox             1
    Wang            2
    Green           1
    Fox             
    Phillips        2
    Loftus          
    Smith           2
    Smith           3
    Wolf            2
    Jones           1
    Liu

Upvotes: 0

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

One method uses case, another nullif():

select ename, nullif(sales, 0) as sales
from t;

If this is an aggregation query:

select ename, nullif(count(*), 0) as sales
from t
group by ename;

Upvotes: 3

Related Questions