Adding count column to sql results

Question

I would like to create a column from SQL results that counts the number of duplicate individual result fields within an SQL result set.

Say I have the following data:

color    flavor
-----    ------
red      cherry
green    lime
red      strawberry
white    tapioca
blue     blueberry
white    vanilla

I would like a result set that would list

red      cherry       1
red      strawberry   2
white    vanilla      1
white    tapioca      2
green    lime         1
blue     blueberry    1

The order doesn't matter so long as it is grouped by color. The main issue is getting the third column (count).

I envision doing this by dumping the initial table into a temporary table and then iterating through the table and appending the count column to, maybe, another temp table (insert).

declare @color string
declare @flavor string
declare @tmp table (color string, flavor string)
insert @tmp
select color,flavor from menu
select top 1 @color=color, @flavor=flavor from @tmp
while (@@rowcount>0)
begin
  [do stuff here]
  [delete first row of @tmp]
  select top 1 @color=color, @flavor=flavor from @tmp
end

I think this is plausible and may be a good direction but I am unsure. Can anyone provide an alternative and/or fill in the pseudocode to get that third column and delete the first row?

score 2 · Accepted Answer · answered Apr 26 '23 at 21:42

2

You can do it using the window funtion row_number() :

select *, row_number() over (partition by color order by (select null))
from mytable
order by color

Demo here

answered Apr 26 '23 at 21:42

SelVazi

10,028
2
13
29

1

This is exactly what I wanted. Thank you. I had no idea I was asking for row_number(), nor that it could be used this way. – Jon Glazer Apr 26 '23 at 22:56
Glad to hear :) Yes row_number() is magical – SelVazi Apr 26 '23 at 23:01

Adding count column to sql results

1 Answers1