List:General Discussion« Previous MessageNext Message »
From:Tom Crimmins Date:February 11 2005 8:48pm
Subject:RE: Need help with historic aggregation of data
View as plain text  
> -----Original Message-----
> From: Daevid Vincent
> Sent: Friday, February 11, 2005 14:32
> To: mysql@stripped
> Subject: Need help with historic aggregation of data
> 
> I need to get the aggregate data from various tables for a report.
> 
> The idea is that we audit devices daily on a schedule, and 
> also allow users
> to audit the devices by choosing certain tests to run. It is 
> also the case
> that new tests are added daily. So the scheduled test today 
> has more tests
> than yesterdays and that has more than the day before's, etc.
> 
> I want to get a report that shows ALL tests ever run on the 
> device in it's
> lifetime, but only the most recent of each test (and the date 
> it was from).
> 
> So if I ran tests like this:
> 
> Date	 Device   Test    Result
> -----  ------   -----   ------
> 
> 02/01    1       100     [scheduled] blah blah blah...
> 02/01    1       101     [scheduled] blah blah blah...
> 02/01    1       102     [scheduled] blah blah blah...
> 02/01    1       105	 [one off] foo foo foo...
> 
> 02/02    1       100     [scheduled] blah blah blah...
> 02/02    1       101     [scheduled] blah blah blah...
> 02/02    1       102     [scheduled] blah blah blah...
> 02/02    1       103     [scheduled] ble ble ble...
> 02/02    1       106	 [one off] bar bar bar...
> 
> 02/03    1       100     [scheduled] blah blah blah...
> 02/03    1       101     [scheduled] blah blah blah...
> 02/03    1       102     [scheduled] blah blah blah...
> 02/03    1       103     [scheduled] ble ble ble...
> 02/03    1       104     [scheduled] blo blo blo...
> 
> 02/01    2       100     [scheduled] blah blah blah...
> 02/01    2       101     [scheduled] blah blah blah...
> 02/01    2       102     [scheduled] blah blah blah...
> 02/01    2       106	 [one off] bar bar bar...
> 	... Etc ...
> 
> What I'd expect to get back for device 1 is
> 
> Test	Date
> ----	-----
> 100	02/03   this is more current than others
> 101   02/03	  this is more current than others
> 102   02/03   this is more current than others
> 103   02/03   this is more current than others
> 104   02/03   this is more current than others
> 105   02/01   since this was run long ago once
> 106   02/02   since this was ran recently
>

SELECT device,test,MAX(date) FROM my_table GROUP BY device,test ORDER BY
device,test

This will give you all devices.

and

SELECT test,MAX(date) FROM my_table WHERE device=1 GROUP BY test ORDER BY
test

will give you results for device 1. 

> My actual tables are pretty huge, and I'll spare you them. I 
> also am coding
> this in PHP, in case I need to split this task up somehow. We 
> are using
> v4.0.18 and can't change.
> 
> I'm hoping there is some magic incantation of MAX(), GROUP 
> BY, DISTINCT,
> that will harvest this info for me.
> 
> Thanks in advance,
> 
> Daevid.

---
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa 
Thread
Need help with historic aggregation of dataDaevid Vincent11 Feb
  • Re: Need help with historic aggregation of dataHomam S.A.11 Feb
RE: Need help with historic aggregation of dataTom Crimmins11 Feb