Skip to content
Advertisement

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

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?

Advertisement

Answer

In sql, it’s resolved using basic aggregation

select interactionuserkey, count(*)
from your_table
group by interactionuserkey
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement