Blake Binkley wrote:
>
> here is an example of what I'm looking for:
>
> we have a people counter at an office every person has a unique id badge
> and sensors all over the office the database stores the following info:
>
> ID | hour | day | month | year | location
> 123 | 12 | 15 | 6 | 1999 | front office
> 123 | 12 | 16 | 6 | 1999 | front office
> 145 | 12 | 16 | 6 | 1999 | front office
> 123 | 12 | 16 | 6 | 1999 | front office
> 145 | 12 | 16 | 6 | 1999 | front office
> 123 | 13 | 16 | 6 | 1999 | front office
>
> all I need is a way to know that 1 person (not who it was but just a
> count) was in the front office with a difference of 1 hour between his
> first visit and his last visit, IE: user 123's first visit was on the
> 15th at noonish and his last visit was at 1 pm-ish on the 16th the
> fact that user 145 entered the front office at noonish 2 times on the
> 16th does not make him a valid increment and user 123 being in the front
> office at noonish also does not count
>
> so with the small table above I would like the following result out of a
> query:
>
> | count(repeat_over_an_hour) |
> | 1 |
Hi Blake
Is there a way to get those duplicates out of the table?
They will make problems, since mysql doesn't provide COUNT( DISTINCT ... ).
So the only thing you can get is this:
SELECT DISTINCT
pc1.ID
FROM
people_counter AS pc1
,people_counter AS pc2
WHERE
pc1.ID = pc2.ID
AND pc1.location = pc2.location
AND pc1.hour < pc2.hour
AND pc1.day = pc2.day
AND pc1.month = pc2.month
AND pc1.year = pc2.year
So you have to count yourself on the client side.
Tschau
Christian
