CODEWITHSUNDEEP
sqlsql-server
Diogo Santos
Diogo Santos

Reputation: 830

Set value based on count distinct

Let's say that I have the following data on tbl:

Label | Color
X     | color1
X     | color1
Y     | color1
Y     | color2

The sample output should be:

X | color1
Y | misc

So basically, if the respective label only has one type of color I want it to be that type, else I want it to say misc.

I did the following which didn't work as I wanted, which the next step would be applying a case into the count:

select label,count(distinct color) from @tbl
group by label,color

Upvotes: 1

Views: 65

Answers (1)

Martin Smith
Martin Smith

Reputation: 453687

you can use MAX or MIN to get the single value to use where appropriate.

And should be grouping just by label

select label,
       case when count(distinct color) = 1 THEN MAX(color) ELSE 'misc' END
from @tbl
group by label

It will generally be more efficient though to check if max equals min rather than doing a distinct count

select label,
       case when MAX(color) = MIN(color) THEN MAX(color) ELSE 'misc' END
from @tbl
group by label

There isn't much to choose between the two in efficiency if you have an index on label, color but if you only have an index on (label) INCLUDE (color) or have no useful index at all the MAX/MIN approach will have to do less work (just needs a single grouping operation on label and to keep track of two values within each group - not identify the number of unique values in each group)

Upvotes: 6

Related Questions