CODEWITHSUNDEEP
postgresqlnullgroup-by
Sathesh S
Sathesh S

Reputation: 1323

Default value in select query for null values in postgres

I have a table with sales Id, product code and amount. Some places product code is null. I want to show Missing instead of null. Below is my table.

salesId     prodTypeCode    amount
1           123              150
2           123              200
3           234             3000
4           234              400
5           234              500
6           123              200
7           111              40
8           111              500
9                           1000
10          123              100

I want to display the total amount for every prodTypeCode with the option of If the prodTypeCode is null then Missing should be displayed. 

select (CASE WHEN prodTypeCode IS NULL THEN
   'Missing'
    ELSE
    prodTypeCode
    END) as ProductCode, SUM(amount) From sales group by prodTypeCode

Above query giving error. Please suggest me to overcome this issue. I ahve created a SQLFIDDLE

Upvotes: 24

Views: 46267

Answers (3)

prettybug
prettybug

Reputation: 168

I tried both answers and in my case both did not work. What ended up helping was this:

SELECT 
COALESCE(NULLIF(prodTypeCode,''), 'Missing') AS ProductCode,        
SUM(amount)
From sales s
group by prodTypeCode;

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1270653

Perhaps you have a type mismatch:

select coalesce(cast(prodTypeCode as varchar(255)), 'Missing') as ProductCode,     
       SUM(amount)
From sales s
group by prodTypeCode;

I prefer coalesce() to the case, simply because it is shorter.

Upvotes: 12

Bohemian
Bohemian

Reputation: 425238

The problem is a mismatch of datatypes; 'Missing' is text, but the product type code is numeric.

Cast the product type code to text so the two values are compatible:

select (CASE WHEN prodTypeCode IS NULL THEN
   'Missing'
    ELSE
    prodTypeCode::varchar(40)
    END) as ProductCode, SUM(amount) From sales group by prodTypeCode

See SQLFiddle.

Or, simpler:

select coalesce(prodTypeCode::varchar(40), 'Missing') ProductCode, SUM(amount)
from sales
group by prodTypeCode

See SQLFiddle.

Upvotes: 47

Related Questions