Hello Sql Insert with same id but different values in another column ?

Question

Hi All,

I need the requirements of inserting multiple rows of same ID

My Table Data:

Steam_id Skin_name
steam_id 1 skin_name1
steam_id 1 skin_name2
steam_id 1 skin_name3

steam_id 2 skin_name1
steam_id 2 skin_name2
steam_id 2 skin_name3

Required Output Should be,

Steam_id Skin_name
steam_id 1 3 ( total number of skin )
steam_id 2 3 ( total number of skin )

Please help me to achieve this output.

Thanks.

Answer 1

Hi @Akshay ,

Welcome to Microsoft Q&A!

You could use Viorel's answer which is the best one.

Please also refer below as alternative approaches:

select distinct  Steam_Id, count( Skin_name) over (partition by  Steam_Id) Skin_name  
from (select distinct * from mytable) a  

OR

SELECT distinct Steam_Id,dense_rank() over (partition by Steam_Id order by Skin_name)  + dense_rank() over (partition by Steam_Id order by Skin_name desc) - 1 Skin_name  
FROM mytable  

Output:

Steam_Id	Skin_name  
steam_id 1	3  
steam_id 2	3  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Check this statement:

insert MyOutputTable (Steam_Id, Skin_name)
select Steam_Id, count( distinct Skin_name)
from MyTable
group by Steam_Id

To query without inserting:

select Steam_Id, count( distinct Skin_name)
from MyTable
group by Steam_Id