How to count specific data

Zaran 21

Hi,

Could you please help me in the T_SQL code for the folowing sample?

I have a table (T1) with the following data:

ID, Name, Count

1,AA,0

1,AA,0,

1,BB,0

2,BB,0

2,AA,0

2,AA,0

2,BC,0

2,BC,0

2,CC,0

3,AA,0

3,BC,0

4,DD,0

5,AA,0

I nee to have the below result:

ID,Name,Count

1,AA,1

1,AA,1

1,BB,2

2,BB,1

2,AA,2

2,AA,2

2,BC,3

2,BC,3

2,CC,4

3,AA,1

3,BC,2

4,DD,1

5,AA,1

Yitzhak Khabinsky 27,196 Reputation points

2022-02-16T19:43:27.567+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
LiHong-MSFT 10,061 Reputation points

2022-02-17T01:59:46.893+00:00

Hi @Zaran
If the sequence of Names in each ID doesn't matter, then refer to Naomi's answer.
Olaf Helper 47,621 Reputation points

2022-02-18T08:04:34.63+00:00

I nee to have the below result:

And what's the logic for the result? Just having a look at your result, I think it don't make any sense.

1 answer

Naomi Nosonovsky 8,906 Reputation points

2022-02-16T20:03:15.873+00:00

;with cteResult as (select ID, Name, DENSE_RANK() over (partion by ID order by name) as Count from @t)
select * from cteResult order by ID, Name

The only problem is that it would not be exactly the same result as you shown.
Was this answer helpful?

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Your answer