CODEWITHSUNDEEP
sqlsql-servert-sql
Marcel Grüger
Marcel Grüger

Reputation: 946

SQL Group By and changing the grouped values

I have a table like this:

1 | Madrid | 45000
2 | Berlin | 35000
3 | Berlin | 65000

Now I want to show a result like this:

1 | Madrid | 45000
2 | Berlin | "Different Values"

So basically I want to use a "Group By" and if it is grouped, then change the value of some columns to a manual string. I thought about using a view, update all values in this view to the string where i have duplications of the grouped column and then use the real query. I even thought about implementing an assembly into the sql server that does this, but I don't find any good tutorials on this, only that you can do it. Or has someone an even better idea? (The real tables used here are huge and the sql query does take sometimes up to 3 minutes to perform, so I made this example simple and I didn't wanted to work here with counts on every column to group, because that could take more than just a few minutes.

Upvotes: 0

Views: 1879

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I would do this as:

select min(id) as id, city,
       (case when min(value) = max(value)
             then cast(max(value) as varchar(255))
             else 'Different Values'
        end) as result
from t
group by city;

In fact, I might use something more informative than "different values", such as the range:

select min(id) as id, city,
       (case when min(value) = max(value)
             then cast(max(value) as varchar(255))
             else cast(min(value) as varchar(255)) + '-' + cast(max(value) as varchar(255))
        end) as result
from t
group by city;

Upvotes: 1

juergen d
juergen d

Reputation: 204784

Something like this should work

select min(id) as id, 
       name,
       case when count(*) = 1 
            then cast(sum(value) as varchar)
            else 'Different values'
       end as value
from your_table
group by name

Upvotes: 2

Related Questions