Problem exporting a MySql database table
Moderator: General Moderators
Problem exporting a MySql database table
I am trying to export a Mysql table into a CSV file...The problem is that I can't get to calculate the total of the values in a column without click drag the colums and get the summation...Is it possible to get the total of the column values automatically while exporting?
Re: Problem exporting a MySql database table
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.
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.
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.
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 |
# +-------------+---------+---------+-------+----------+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 |
# +----+----+------+----+------+----+------+----+------+----+------+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.