×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Find average temp for all samples using SQL

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)

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"];
}
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

RE: Find average temp for all samples using SQL

(OP)
Thanks!!
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close