CODEWITHSUNDEEP
sqlcountcase
kpgu1718
kpgu1718

Reputation: 97

SQL CASE SUBQUERY COUNT

I have table with 2 col :

UID    NAME
-----------
111    AAA
222    BBB

Customer will enter the name and I have to retrieve UID with respective value. If name won't present in the rows, it has to retrieve 000, not like no rows.

I am trying to write query like this:

SELECT  
   CASE UID 
      WHEN Count(*) = 0 THEN '000'
      ELSE UID 
   END
FROM table1   
WHERE NAME ='XXX'

Please help me in this regard. Thanks in advance...

Upvotes: 1

Views: 1019

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

If UID is an integer, then you need to take casts into account:

select coalesce(cast(max(uid) as char(3)), '000')
from table1
where name = 'XXX'

The cast is intended to be to the type of UID, which seems to be char(3) in your example.

When there are no matching rows, then the max() function returns NULL. The coalesce() turns this into the value you are looking for.

Upvotes: 1

Canburak Tümer
Canburak Tümer

Reputation: 1053

You have error in case

SELECT case when count(UID) = 0 THEN '000' ELSE UID end FROM table1 where name = 'XXX'

sqlfiddle :http://www.sqlfiddle.com/#!2/257ea/1

Upvotes: 0

Galbarad
Galbarad

Reputation: 453

try this 

select case
           when max(id) is null then
            0
           else
            max(id)
       end
  from table1
 where name = 'b'

Upvotes: 0

Related Questions