Very difficult (form me!) SELECT query

[IGOR_ITALY]

Hi all,

I have created a table in my DB to make a poll, and now I have the necessity
to count the votes using an IP filter.

This is my table:

ID (auto-increment colum)

DESTINATION (1 = Italy, 2 = Franch, 3 = Usa)

DATE (votation date)

IP (IP of the votants)

Now, I have to count how many votes receive the destination 1 (Italy),
destination 2 (Franch) and destination 3 (Usa) COUNTING THE IP ADDRESS ONE
TIME PER DATE. That is to say that I want to eliminate all the people (IP)
that have voted more than one time in the same day.

This is an example of my data in the table:

(ID, DESTINATION, DATE, IP)
1 1 29/10/2008 18.29.41 87.16.176.3
2 2 30/10/2008 18.29.41 87.16.176.3
3 3 30/10/2008 18.30.08 87.16.176.3
4 3 30/10/2008 18.30.13 87.16.176.3
5 3 30/10/2008 18.30.19 87.16.176.3
6 3 30/10/2008 18.30.28 87.16.176.3

The IP = "87.16.176.2" in the day 30/10/2008 has voted for 5 times, I would
like to count ONLY his first vote:

DESTINATION = 1 IN THE DAY 29
(ONLY) DESTINATION = 2 IN THE DAY 30

I have written this SQL query:

SELECT
IP, Data, COUNT(Destination) AS Dest
FROM
Statistiche
GROUP BY IP, Data

but this is the result:

(IP, Data, Dest)
87.16.176.3 30/10/2008 18.29.41 1
87.16.176.3 30/10/2008 18.30.08 1
87.16.176.3 30/10/2008 18.30.13 1
87.16.176.3 30/10/2008 18.30.19 1
87.16.176.3 30/10/2008 18.30.28 1
87.16.176.3 31/10/2008 10.31.53 1
87.16.176.3 31/10/2008 10.31.58 1
87.16.176.3 31/10/2008 10.32.04 1
87.16.176.3 31/10/2008 10.32.42 1

I've tried to use the DISTINCT(Data) command but it doesen't work...

Is it possible to do what I need to do or I have to use some algorithm to do
it programmatically and not using an SQL instruction?

Some help?

 

[John Spencer]

If you want to count the earliest the query is a bit trickier than if you want
to count ONE of the votes on the date

The following base query selects only one (more or less random) vote per day
per IP. You can use this as the source for a count.
SELECT IP, DateValue(Data), First(Destination) AS Dest
FROM Statistiche
GROUP BY IP, DateValue(Data)

If you need the Earliest Data then the base query is more complex

SELECT S.Ip, S.Data, S.Destination
FROM Statistiche as S INNER JOIN
(SELECT S1.IP, Min(S1.Data) as TheDate
FROM Statistiche as S1
GROUP BY S1.IP, DateValue(S1.Data)) as S2
ON S.IP = S2.IP
AND S.Data = S2.TheDate

Now use that query to as the source for your statistics

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County