Find average temp for all samples using SQL
Find average temp for all samples using SQL
(OP)
I have two tables:
sample has three columns; s_id (int), s_start(timestamp), and s_end(timestamp)
temp has two columns; t_timestamp (timestamp), and t_temp (real)
I want to do the following:
for each row in sample, return the s_id and the average temperature from temp between the times s_start and s_end.
I can do a foreach loop in my PHP code (PHPish psudocode follows)
or I can make a very long query in the same way and use a UNION to join all the cycles through the foreach loop together. However, there has to be a way to make one concise query to handle this, no?
Kevin
sample has three columns; s_id (int), s_start(timestamp), and s_end(timestamp)
temp has two columns; t_timestamp (timestamp), and t_temp (real)
I want to do the following:
for each row in sample, return the s_id and the average temperature from temp between the times s_start and s_end.
I can do a foreach loop in my PHP code (PHPish psudocode follows)
CODE
$value=array()
$sample = "SELECT * FROM sample"
foreach ($sample as $key=>$value){
$result = "SELECT avg(t_temp) AS ave FROM temp WHERE t_timestamp > $value["s_start"] AND t_timestamp < $value["s_end"]";
$value[$key]["ave"] = $result["ave"];
}
$sample = "SELECT * FROM sample"
foreach ($sample as $key=>$value){
$result = "SELECT avg(t_temp) AS ave FROM temp WHERE t_timestamp > $value["s_start"] AND t_timestamp < $value["s_end"]";
$value[$key]["ave"] = $result["ave"];
}
Kevin
Talk To Other Members
RE: Find average temp for all samples using SQL
FROM sample S INNER temp T ON T.t_timestamp BETWEEN S.s_start AND S.s_end
GROUP BY S.s_id
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Find average temp for all samples using SQL
This opens up a new window for me. I've never used anything except NATURAL INNER JOINs. I've never even considered anything besides PK, FK joins on equal values, so this will have some broad-reaching consequences in a lot of my projects.
I'll try this in my PostgreSQL database, but a stupid MS-Access test doesn't like the BETWEEN comparator. However, the following equivalent statement does work in Access:
SELECT S.s_id, AVG(T.t_temp) AS ave
FROM sample S INNER JOIN temp T ON (T.t_timestamp > S.s_start AND T.t_timestamp < S.s_end)
GROUP BY s_id