I think there are some details missing from your description. How are you exporting the CSV -- phpMyAdmin? Where do you "click drag the colums and get the summation" -- in a spreadsheet program?
Assuming my guesses were close, here is an example. The example uses a table named "money" which holds the number of pennies, nickels, dimes, and quarters involved in some transactions.
Code: Select all
DESCRIBE `money`
# +-------------+------------------+------+-----+---------+----------------+
# | Field | Type | Null | Key | Default | Extra |
# +-------------+------------------+------+-----+---------+----------------+
# | transaction | int(10) unsigned | NO | PRI | | auto_increment |
# | pennies | int(10) unsigned | NO | | | |
# | nickels | int(10) unsigned | NO | | | |
# | dimes | int(10) unsigned | NO | | | |
# | quarters | int(10) unsigned | NO | | | |
# +-------------+------------------+------+-----+---------+----------------+
SELECT * FROM `money`
# +-------------+---------+---------+-------+----------+
# | transaction | pennies | nickels | dimes | quarters |
# +-------------+---------+---------+-------+----------+
# | 1 | 4 | 9 | 5 | 1 |
# | 2 | 10 | 10 | 4 | 2 |
# | 3 | 6 | 1 | 6 | 5 |
# +-------------+---------+---------+-------+----------+
Using GROUP BY WITH ROLLUP, we can create a result set that includes totals for both columns and rows. If we left out the last two SUM()s, it would just be totals for columns.
Code: Select all
SELECT `transaction` AS `tn` # transaction number
, SUM(`pennies`) AS `pc` # pennies count
, SUM(`pennies` * 0.01) AS `pv` # pennies value
, SUM(`nickels`) AS `nc` # nickels count
, SUM(`nickels` * 0.05) AS `nv` # nickels value
, SUM(`dimes`) AS `dc` # dimes count
, SUM(`dimes` * 0.10) AS `dv` # dimes value
, SUM(`quarters`) AS `qc` # quarters count
, SUM(`quarters` * 0.25) AS `qv` # quarters value
, SUM(`pennies`
+ `nickels`
+ `dimes`
+ `quarters`) AS `cc` # coins count
, SUM(`pennies` * 0.01
+ `nickels` * 0.05
+ `dimes` * 0.10
+ `quarters` * 0.25) AS `cv` # coins value
FROM `money`
GROUP BY `transaction` WITH ROLLUP
# +----+----+------+----+------+----+------+----+------+----+------+
# | tn | pc | pv | nc | nv | dc | dv | qc | qv | cc | cv |
# +----+----+------+----+------+----+------+----+------+----+------+
# | 1 | 4 | 0.04 | 9 | 0.45 | 5 | 0.50 | 1 | 0.25 | 19 | 1.24 |
# | 2 | 10 | 0.10 | 10 | 0.50 | 4 | 0.40 | 2 | 0.50 | 26 | 1.50 |
# | 3 | 6 | 0.06 | 1 | 0.05 | 6 | 0.60 | 5 | 1.25 | 18 | 1.96 |
# | | 20 | 0.20 | 20 | 1.00 | 15 | 1.50 | 8 | 2.00 | 63 | 4.70 |
# +----+----+------+----+------+----+------+----+------+----+------+
In phpMyAdmin, there is an "Export" link in the "Query results operations" box below the results table on the "Browse" page. You can export the query results in CSV format.
The problem with this is that if you import the CSV into a spreadsheet program the totals will be treated as fixed data rather than the result of a summation function and will not change based on changes you make to other cells in the spreadsheet.
Related topic: (broken)
98448 (crosstab queries)
Edit: This post was recovered from search engine cache.