May 7, 2003 at 12:27 am
Hi,
I have a select statment with a group by clause.
eg : select gender, count(*) from ulist where status=1 group by gender
There are lots of enteries in the database which dont have a gender associated with them. So as the result of the query it shows a empty field for all the records with no gender specified and then shows count for male and female respectively.
64
Male 75
Female 63
I want to replace the null or empty field with a value like 'none' so that it shows
None 64
Male 75
Female 63
I tried using replace command in the select query for the gender field but no use.
Can you please suggest how can i change the empty fields returned by the select query?
Thanks and regards
Hitendra
May 7, 2003 at 12:29 am
Try :
Select IsNull(gender,'None'), count(*) from ulist where status=1 group by gender
May 7, 2003 at 12:33 am
No I tried that earlier but it doesn't work. It still gives a empty field instead of showing 'None'
May 7, 2003 at 1:14 am
Is your gender field empty or null?
By the sounds of other posts, it empty rather than null and so you should use a case statement:
select case
when gender <> '' then gender
else 'None'
end as gender
from ulist .....
IsNull only works on null fields.
Jeremy
May 7, 2003 at 1:31 am
Thanks, this was simply a perfect solution. I never knew we can use a case statement within select.
Hey i would like to know more about such functions that can be induced in the select query this will decrease a lot of programming.
Where can i get a resource for such functions usable with select query?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy