I would like to know if it is possible in an SQL query to replace some values by something else, or if I need to do that in post-processing.
Let me explain. I have this table:
|username| accepted |
|--------|----------|
| toto | NULL |
|--------|----------|
| foo | 0 |
|--------|----------|
| Rick | 1 |
|--------|----------|
| bar | 1 |
|--------|----------|
I want to know the numbers of row per value of accepted
(nullable bit). I'm running this query:
SELECT [accepted], count(*) FROM my_table GROUP BY [accepted]
Which should return:
NULL 1
false 1
true 2
Is there some way to replace the accepted
values by more meaningful labels? Ideally I would like to have something like:
not_available 1
not_accepted 1
accepted 2
Is that feasible with SQL server 2008 R2?
Thx.
Using the REPLACE() function will allow you to change a single character or multiple values within a string, whether working to SELECT or UPDATE data.
If you have a few values:
SELECT CASE [accepted]
WHEN 0 THEN 'not_accepted'
WHEN 1 THEN 'accepted '
ELSE 'not_available' END AS accepted
, count(*)
FROM my_table
GROUP BY CASE [accepted]
WHEN 0 THEN 'not_accepted'
WHEN 1 THEN 'accepted '
ELSE 'not_available' END
Use a CASE
SELECT
CASE [accepted]
WHEN 1 THEN 'accepted'
WHEN 0 THEN 'not_accepted'
ELSE 'not_available' --NULL
END AS [accepted], count(*)
FROM my_table
GROUP BY --try [accepted] by itself first
CASE [accepted]
WHEN 1 THEN 'accepted'
WHEN 0 THEN 'not_accepted'
ELSE 'not_available' --NULL
END
You may have to use the whole CASE in the GROUP BY. Unless you do this
SELECT
CASE [accepted]
WHEN 1 THEN 'accepted'
WHEN 0 THEN 'not_accepted'
ELSE 'not_available' --NULL
END AS [accepted], CountOfAccepted
FROM
(SELECT [accepted], count(*) AS CountOfAccepted
FROM my_table GROUP BY [accepted]) foo
SELECT
CASE [accepted]
WHEN 1 THEN 'accepted',
WHEN 0 THEN 'not accepted',
ELSE 'not available'
END AS [accepted],
count(*)
FROM my_table GROUP BY [accepted]
?
Not sure if this is exactly right and don't have SQL Server available to test now, but should be something like that.
EDIT: aww, already posted
yeah it is there ... you can modify your query as below -
SELECT decode(accepted,NULL,'not_available' ,1,'accepted',0,'not_accepted'),count(*)
FROM my_table GROUP BY
decode(accepted,NULL,'not_available' ,1,'accepted',0,'not_accepted')
it should give output as you want..
may be decode function is not there then you can use case
over there..
SELECT
case accepted
WHEN 1 THEN 'accepted'
WHEN 0 THEN 'not_accepted'
ELSE 'not_available'
end,count(*)
FROM my_table GROUP BY
case accepted
WHEN 1 THEN 'accepted'
WHEN 0 THEN 'not_accepted'
ELSE 'not_available' end
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With