qtrimble Posted March 19, 2009 Share Posted March 19, 2009 I have a MySQL database which contains water quality data from sampling events. There are multiple records for each of the sample events. Each one of the records from the sample event contain the sample date and a site identification value (numeric). What I need to do is search all of the records that exist in the database which occur on the same date with the same site identification value. Each of the water quality measurements for a given sample event, for a given site, need to be averaged. HERE IS THE QUESTION: With the information that i provided above, which scenario would be the best: 1) Create a View in MySQL 2) Use PHP to do this and store averaged values in an array I'm not very skilled in these technologies so I will need some details from your answer to get me started. Much appreciation to all who contribute to solving my problem. Link to comment https://forums.phpfreaks.com/topic/150211-need-to-average-values-across-multiple-records-which-have-the-same-id/ Share on other sites More sharing options...
mr_bijae Posted March 19, 2009 Share Posted March 19, 2009 There are arguments for both scenarios you have suggested. The primary consideration is, will PHP be the only API accessing the data? If so then you're fine building it in PHP. If you're going to have other applications getting the data from the database, then you're better off putting it in view or stored procedure. Link to comment https://forums.phpfreaks.com/topic/150211-need-to-average-values-across-multiple-records-which-have-the-same-id/#findComment-788840 Share on other sites More sharing options...
mr_bijae Posted March 19, 2009 Share Posted March 19, 2009 Without knowing your table structure I'm not able to write a specific query for you. however it should be something like this; select avg(waterquality) as quality, siteId, sample_date from quality_table group by Sample_date, siteid This will produce a query that gives one average value, per date, per site id Link to comment https://forums.phpfreaks.com/topic/150211-need-to-average-values-across-multiple-records-which-have-the-same-id/#findComment-788847 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.