Question: I want to get the count of values within a recordset . Currently when I get the recordset it's displayed 

id color
1 red
2 red
3 red
4 blue
5 blue
6 blue
7 black
8 black

Whereas I'm looking for : 

color color_numbers
black 2
blue 3
red 3

Answer: This is an example script of how you can achieve this result.  This is a naive query - and may not fully encapsulate what you are trying to achieve within the overall grouping operations.

create table #temp1
(id int,
color varchar(10)
)


insert into #temp1
select 1, 'red'
union
select 2, 'red'
union
select 3, 'red'
union
select 4, 'blue'
union
select 5, 'blue'
union
select 6, 'blue'
union
select 7, 'black'
union
select 8, 'black'


select color,
count(color) as 'color_numbers'
from #temp1
group by color


drop table #temp1

Read more on GROUP BY

How to use STRING_AGG to concatenate strings with grouping