sums of many columns
Posted 27 May 2003 - 01:16 AM
My question: is there a way to do the same for 288 columns at once, without having to issue 288 SELECT statements or creating one incredibly long SELECT statement like SELECT * , sum( col_one ) AS \'1\', sum( col_two ) AS \'2\', sum( col_3 ) AS \'3\', sum( col_4 ) AS \'4\', sum( col_5 ) AS \'5\' ... etc. ... FROM some_table? :?:
Posted 27 May 2003 - 07:09 AM
If you mean getting 288 sums, I would use php to fetch the coloumn names, then do 288 php generated searches - or simply use php to build your mysql SELECT statement...
And of pure curiousity, why on earth do you have a table design with 288 variables?
Posted 27 May 2003 - 09:42 PM
To satisfy your curiosity: the table(s) contain(s) values collected at 5 minute intervals during a 24 hour cycle. These daily data collections are used to generate a graph of cumulative values for display of various timeframes (last quarter, previous half year, Fridays only, all year except certain days, etc.) in the 24 hour format. For reliability each data point needs to be editable / reviewable. All this applies to 21 different data sets.
I had considered other designs, but found this the most space (and hopefully time-) conserving approach.
I had hoped to find something in the way of mysql_fetch_array or \"mysql_fetch_col\" (if that existed!) but had no success. So, I\'ll probably have to use a clunky SELECT statement with 288 SUM(..)s
Posted 28 May 2003 - 08:29 AM
And... you COULD have made a table with two coloumns:
TIME DATA (where TIME would have 288 different values...)
But I will give you that reability is a little better for 288 coloumns...
Posted 28 May 2003 - 09:33 AM
I wasn\'t too concerned about creating the SELECT statement, I was much more concerned about the poor server having to execute it! (That is also my reason for maintaining a \"day of week\" field - to prevent a recalculation each time a certain weekday is requested)
But then I visited a South African newspaper website (which must\'ve crashed right then, because it showed their error message) whose SQL statement contained about 500 values in their call! Now I don\'t feel so demanding anymore.
Your 2 column solution would require at least 3, because I would have to keep track of the date in addition to the time and the value - otherwise the filtering would not be possible. Plus the \"day of week\" field, a check field for verification purposes, and possibly some \"marker fields\".
Thanks again for your support!
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users