Thanks a lot Livingston,
I changed your query to
SELECT uid, hostname, COUNT(DISTINCT timestamp) AS visits
FROM mslop_login_activity
GROUP BY uid, hostname
ORDER BY uid, hostname
It gave me following results:-
uid | hostname | visits
1 | 172.24.1.143 | 3
1 | 172.24.1.161 | 1
2 | 172.17.77.253 | 1
2 | 172.24.1.244 | 1
3 | 157.191.122.36 | 20
3 | 172.24.1.143 | 18
3 | 172.24.1.161 | 10
3 | 172.24.1.166 | 8
4 | 157.191.122.36 | 11
4 | 172.17.64.224 | 2
4 | 172.17.64.94 | 2
4 | 172.17.65.129 | 1
4 | 172.17.66.44 | 1
4 | 172.17.71.161 | 1
4 | 172.17.73.253 | 1
4 | 172.17.76.74 | 2
4 | 172.24.1.161 | 4
4 | 172.24.1.163 | 2
4 | 172.24.1.243 | 6
4 | 172.24.1.244 | 1
4 | 172.26.2.179 | 1
35 | 172.24.1.143 | 2
64 | 172.24.1.143 | 1
65 | 172.24.1.161 | 2
73 | 157.191.122.36 | 8
83 | 157.191.122.36 | 77
83 | 5.10.88.12 | 2
83 | 5.10.88.14 | 1
93 | 157.191.122.36 | 15
As you can see that Hostnames, IPs are still being duplicated, do you think we can tweak the query in such a way that all the common Hostnames group together and give the total number of Occurrences of each as a total no of visits?
Thanks.