Page 1 of 1

Problem exporting a MySql database table

Posted: Wed Nov 11, 2009 7:10 am
by rbhoumik
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

Posted: Wed Nov 11, 2009 12:18 pm
by McInfo
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.