GROUP BY multiple values in same column

sql sql-server tsql

12,845

Solution 1

case when prefix like '%[0-9]%' then '0' else prefix end

You obviously also need this as the expression in the GROUP BY:

select 
    NewPrefix = case when prefix like '%[0-9]%' then '0' else prefix end, 
    code, 
    stat, 
    complete, 
    COUNT(*) as Count
from table
group by 
    case when prefix like '%[0-9]%' then '0' else prefix end, 
    code, stat, complete
order by 
    case when prefix like '%[0-9]%' then '0' else prefix end, 
    code, stat, complete

Solution 2

Try this :

select case when prefix not like '%[^0-9]%' then prefix else '0' end as prefix, code, stat, complete, COUNT(*) as Count
from table
group by case when prefix not like '%[^0-9]%' then prefix else '0' end, code, stat, complete
order by prefix, code, stat, complete

Check This. Looks similar "ISNUMERIC()"

12,845

Author by

Ultracoustic

I can programming.

Updated on June 17, 2022

Comments

Ultracoustic about 2 years
I have this SQL Query:
```
select prefix, code, stat, complete, COUNT(*) as Count
from table
group by prefix, code, stat, complete
order by prefix, code, stat, complete
```
The column 'prefix' is an alphanumeric value (0-9a-zA-z). What I want is to make it so that if the value of prefix is a number, to make the number equal to 0. If it is a letter, it will keep its value. I have tried to add the following line beneath the group by clause:
```
(case when prefix like '%[0-9]%' then 0 else prefix end)
```
But I get an error "Conversion failed when converting the varchar value 'J' to data type int.".

What is causing this error? How can I get the 'prefix' column to display either 0 or a letter?
Recursive almost 10 years

will the second statement work, given the prefix column in varchar i.e alphanumeric values and without a convert? we cannot run a numeric convert also on alpha numeric column
Gordon Linoff almost 10 years

@anand . . . isnumeric() takes a string as an argument.
Recursive almost 10 years

But it has few limitations it seems.(link in my answer).Thanks for the info
Gordon Linoff almost 10 years

@Anand . . . isnumeric() is fine for the data described in the question, which consists only of numbers and letters. There is the exception of scientific notation.
Nick.McDermaid almost 10 years

not only that you're mixing numbers and strings which will simply throw the original 'conversion failed' error.
John Willemse almost 10 years

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post.
John Smith almost 10 years

@ElectricLlama it's because you didn't put '' around the 1 and 0, so when it treats the column as an INT data type, but when the 'else' clause is triggered it has to be varchar. If you do it like I did above it will work. Just tested it.
John Smith almost 10 years

@JohnWillemse it solves his problem. read the last sentence of the post- "What is causing this error? How can I get the 'prefix' column to display either 0 or a letter?" My solution solves this problem without raising an error, like his previous method did.
Ultracoustic almost 10 years

Awesome! This worked once I removed the "prefix =" part from the select part! Thank you!
Nick.McDermaid almost 10 years

When I originally posted my comment there were no quotes around the 0, so my comment was completely valid. Subsequently the post has been changed to include quotes.... further to this you have now incorrectly added quotes around the 1! isnumeric returns a number not a string, so you should not compare it to a string. You'll find more performance issues from implicit casts then you will from using LIKE