Hi all,

I have the table as follows:

table name hits:

Fields: hitsID, ipAdd, date

This table collects ipaddress and date of visit from the visitor online to my site.

now the problem is i want to display all the ip address date wise counting the same ip address and grouping it

my required result set example:

Date : 01/01/2008

IP Address: 206.13.20.12 (22 times)

IP Address: 203.16.20.166 (10 times)

IP Address: 149.212.25.11 (5 times

Date : 01/02/2008

IP Address: 206.13.20.12 (2 times)

IP Address: 203.16.20.166 (1 times)

IP Address: 149.212.25.11 (15 times

Date : 01/03/2008

IP Address: 206.13.20.12 (22 times)

IP Address: 203.16.20.166 (11 times)

IP Address: 149.212.25.11 (152 times

 Please help me how can i get result set. I tried using

SELECT ipAdd, date FROM hits GROUP BY date

but did not work

Thanks

KT

http://www.wholesaleclothingbiz.com

SELECT

    CONVERT(date, varchar(10), 101) as DateDay,

    ipAdd AS IpAddress,

    COUNT(*) AS NumOfTimes

FROM hits

GROUP BY CONVERT(date, varchar(10), 101), ipAdd

Thanks for the help.

I tried your statement, but it won't return the result set i want. It returns

date and 1 ip address and the date repeats, for example if there is 10 different ip address on the same day, it repeats the date 10 times.

what i want is Date and list all the ip address on that day under the date, and repeat the same for another day.

thanks again

A SELECT statement isn't designed to do what you want to do. You will need to either write code and process the data from the SELECT statement I sent to you, or write a report using that SELECT statement.

That being said, you could write something like this (although I make no promises on how well it will perform).

Which will give you results like these: 

Date: 01/01/2008
IP Address: 149.212.25.11 (5 times)
IP Address: 203.16.20.166 (4 times)
IP Address: 206.13.20.12 (3 times)
Date: 01/02/2008
IP Address: 149.212.25.11 (4 times)
IP Address: 203.16.20.166 (3 times)
IP Address: 206.13.20.12 (2 times)
Date: 01/03/2008
IP Address: 149.212.25.11 (3 times)
IP Address: 203.16.20.166 (2 times)
IP Address: 206.13.20.12 (1 times)