CSV export has different values to SQL output

user_495488 0
Viewed 40

In phpmyadmin in a mariadb install, I have a SQL statement

SELECT `datepaid`, SUM(`total`) FROM `tblinvoices` WHERE `status` = "paid" AND `total` > 0 GROUP BY YEAR(datepaid)*100 + MONTH(datepaid) ORDER BY `datepaid` ASC

The output is as expected, I get a list of totals grouped by month, ordered by month. The last 5 rows look like this:

2020-06-09 13:01:31    4485.56
2020-07-07 12:36:25    4519.69
2020-08-03 13:02:29    3685.26
2020-09-26 05:49:25    4371.98
2020-10-10 16:47:32    5135.75

At the bottom of the list of all rows, I check all, then click export (under the results, not in the top menu).

In the resulting CSV the last 5 rows are

"2020-06-09 13:01:31","29.00"
"2020-07-07 12:36:25","130.50"
"2020-08-03 13:02:29","99.00"
"2020-09-26 05:49:25","385.00"
"2020-10-10 16:47:32","46.20"

It looks like the output has included one of the rows in the group, rather than the group total.

Am I doing something wrong (most likely) or is this some sort of bug?

[UPDATE]

Based on the info from @nbk I found a solution and learned something, the INTO command

`INSERT INTO `another_table` SELECT `datepaid`, SUM(`total`) FROM `tblinvoices` WHERE `status` = "paid" AND `total` > 0 GROUP BY YEAR(datepaid)*100 + MONTH(datepaid) ORDER BY `datepaid` ASC
phpmyadmin mysql sql
1 Answers

You can use INTO OUTFILE

Like

SELECT 
    `datepaid`, SUM(`total`)
FROM
    `tblinvoices`
WHERE
    `status` = 'paid' AND `total` > 0
GROUP BY YEAR(datepaid) * 100 + MONTH(datepaid)
ORDER BY `datepaid` ASC

INTO OUTFILE 'C:/tmp/cancelled_orders.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';

Depending onb the system configuration, you must check the flder

user_51935360
Related