How do I aggregate data in sql for multiple rows of data by column name?-CodePudding

hi im new to sql and trying to understand how to work with data structures. I have a table

 fact.userinteraction
interactionuserkey   visitdatecode 
0                    20220404      
1                    20220404      
5                    20220402
5                    20220128

If the interaction userkey number repeats then, i want a column called number of visits. in this case, for interactionuserkey 5, there are 2 total visits since its repeated twice. for interactionuserkey 0, number of visits =1 and so on. Basically, sum duplicates in column 1 and give total count AS number of visits. How do i do this?

CodePudding user response：

In sql, it's resolved using basic aggregation

select interactionuserkey, count(*)
from your_table
group by interactionuserkey