Toughie Question Here....

[james0816]

not exactly sure on how to word it but here goes:

i sort my data from a table in the below format:

a b c d
2003 82 46 1
2004 26 36 1
- 24 19 2 <- same yr 2004
2005 66 48 2


Now, I am trying to take it one step further. My new recordset format would look like this:

a b c d e
2003 82 46 1 Y
2004 26 36 1 N
- 24 19 2 N <- same yr 2004
2004 50 55 - Y <- totaling both lines above (would like to be able to add this line to the recordset)
2005 66 48 2 Y

Is something like this possible?

 

[rac2]

Take a look at WITH ROLLUP available in latest version of MySQL.

http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html

 

[james0816]

ahhh....just did the upgrade to 4.1.12a. this looks to be real tricky

Now I'm getting:

a b c d e
2003 82 46 1
2003 82 46 [NULL]
2004 26 36 1
- 24 19 2
2004 50 55 [NULL]
2005 66 48 2
2005 66 48 [NULL]
[NULL] complete totals on this line

when displaying the data, is it possible to display the one line for the records that only have one entry (i.e. 2003 and 2005) but all the lines for multiples (i.e. 2004)?

 

[rac2]

Possibly but it would be difficult.


My thought at this point is to suggest that you think about separating data storage (and retrieval) from presentation. Formatting the data in the way you wish is feasible in procedural code used to build a web page or a report; or in a stored procedure if you were working with a different RDBMS. But it is really outside the realm of things that can be done with SQL.