GROUP BY multiple values in same column

Question

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?

Could you add sqlfiddle with data samples? – Horaciux Jul 18 '14 at 00:22 — Horaciux, Jul 18 '14 at 00:22

Mitch Wheat · Accepted Answer · 2014-07-19T05:43:16.737

10

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

edited Jul 19 '14 at 05:43

answered Jul 18 '14 at 00:06

Mitch Wheat

295,962
43
465
541

1

Awesome! This worked once I removed the "prefix =" part from the select part! Thank you! – Ultracoustic Jul 18 '14 at 15:59

score 1 · Answer 2 · edited May 23 '17 at 12:33

1

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()"

edited May 23 '17 at 12:33

Community

1
1

answered Jul 18 '14 at 01:41

Recursive

954
7
12

score 0 · Answer 3 · answered Jul 18 '14 at 00:34

0

If prefix can have more than one character, then you might want something like:

(case when prefix not like '%[^0-9]%' then '0' else prefix end)

or:

(case when isnumeric(prefix) = 1 then '0' else prefix end)

answered Jul 18 '14 at 00:34

Gordon Linoff

1,242,037
58
646
786

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 – Recursive Jul 18 '14 at 01:31
@anand . . . `isnumeric()` takes a string as an argument. – Gordon Linoff Jul 18 '14 at 01:43
But it has few limitations it seems.(link in my answer).Thanks for the info – Recursive Jul 18 '14 at 01:47
@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. – Gordon Linoff Jul 18 '14 at 01:59

John Smith · Answer 4 · 2015-08-29T22:12:03.673

-1

I just did something like this at work.

select case when isnumeric(prefix) = '1' then '0' else prefix end as BEST_ANSWER
, code
, stat
, complete
, COUNT(*) as Count
from table
group by prefix, code, stat, complete
order by prefix, code, stat, complete

isnumeric is a TSQL function that tests if it is a valid number, and if so it returns 1. The case statement says, if it equals 1, make it a zero, else return the original value. BTW In general, you want to stay away from 'like' because it is bad for performance.

edited Aug 29 '15 at 22:12

answered Jul 18 '14 at 02:09

John Smith

7,243
6
49
61

1

not only that you're mixing numbers and strings which will simply throw the original 'conversion failed' error. – Nick.Mc Jul 18 '14 at 03:23
1

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](http://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](http://stackoverflow.com/help/privileges/comment). – John Willemse Jul 18 '14 at 08:22
@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 Jul 18 '14 at 14:49
@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. – John Smith Jul 18 '14 at 14:51
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_ – Nick.Mc Jul 20 '14 at 21:46

GROUP BY multiple values in same column

4 Answers4

Linked