sums of many columns

[guw]

I know the syntax for summing a column: SELECT * , sum( col_one ) AS \'1\' FROM some_table .

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? :?:

[pallevillesen]

SELECT sum(col_one + col_two....+col_288) as total288 from some_table;

 

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?

 

P.

[guw]

Yes, you guessed it right: I need to have 288 separate sums.

 

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

[pallevillesen]

Ok, but creating a big select statement using php is easy though...

 

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...

 

P.

[guw]

Thank you for at least participating in the \"philosophical\" part of the issue!

 

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!