Find distinct count of group by user

Question

I want to solve this issue in query @

for Ex :

id  ip 
1   1.2.3.3
2   1.2.3.3
3   1.23.42.2
4   4.4.2.1 

I am looking for

ip        count    ids 
1.2.3.3   2       1,2 
1.23.42.2 1       3 
4.4.2.1   1       4 

I have tried with query -

select count(id) ,ip , id from table group by ip ; 

it not working . how to solve this .

Answer 1

You want to select the IDs for the group as text column that will have text for example '1,2' If that is the case, you'd have to go along the way of traversing all results of

select ip, count(id) from table group by id

by a cursor that remembers @ip and @count, then for each row retrieve all IDs for that group

select id from table where ip = @ip

traversing all results by another cursor and store results inside nvarchar @ids variable that will look like '1,2'

then in the main cursor loop you'd output each row as @ip, @count, @ids

Answer 2

You can use MySQL function GROUP_CONCAT:

select ip, count(id) as count, GROUP_CONCAT(id) AS ids
from table 
group by ip ; 

Answer 3

Using group_concat will do it:

SELECT ip, count(id) as count, group_concat(cast(id as char)) as ids
FROM table 
GROUP BY ip