List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:June 8 1999 6:38pm
Subject:Re: any way to relate between rows?
View as plain text  
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

Thread
any way to relate between rows?Blake Binkley7 Jun
  • Re: any way to relate between rows?Christian Mack8 Jun