Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

How to get count of GROUP BY columns

04 June,2021 by Tom Collins

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'
select 2, 'red'
select 3, 'red'
select 4, 'blue'
select 5, 'blue'
select 6, 'blue'
select 7, 'black'
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

Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on How to get count of GROUP BY columns | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer