MySQL pivot query trouble sorting column by total

Question

I am trying to return results for a matrix that represents the net weight that a country imports from another country.

I have managed to write a query that produces a pivot type table in mysql all data is coming from a single table. I have managed to dynamically create the columns (export countries) and sort rows by the total net weight imported by that country.

Where I am coming undone is sorting the export countries which are the columns. I am easily able to sort them alphabetically, however I need to somehow total each column when I dynamically generate that column and then sort by the sum of that column.

Here is an example of the table before the my query:

REPORTER | PARTNER | NET_WEIGHT | YEAR | COMMODITY
--------------------------------------------------
Spain | USA | 3 | 2010 | wheat
Mexico | France | 5 | 2011 | wheat
Norway | USA | 2 | 2012 | wheat
Egypt | Canada | 5 | 2010 | wheat
Germany | UK | 1 | 2011 | wheat
Peru | France | 3 | 2011 | wheat

This is an example of the structure that I am aiming to achieve.

REPORTER | TOTAL | USA | France | Canada | UK 
------------------------------------------------------
TOTAL | | 5 | 4 | 3 | 3 
------------------------------------------------------
Spain | 9 | 3 | 4 | 2 | NULL
Egypt | 6 | 2 | NULL | 1 | 3
Germany | 3 | 1 | NULL | NULL | NULL

Here is the query I have produced so far which produces something similar to the above table, however not sorted correctly:

SET @@group_concat_max_len = 500000;
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('GROUP_CONCAT(IF(`Partner` = ''', `Partner`,''', `NetWeight`, NULL)) AS ''',`Partner`,'''') 
ORDER BY `Partner` ASC)
INTO @sql FROM `tblAnnualData`;
SET @sql = CONCAT('SELECT `Reporter`,SUM(`NetWeight`) AS Total,', @sql,' FROM `tblAnnualData` 
 WHERE `Commodity` = ''wheat'' 
 AND `Year` = 2013 
 GROUP BY `Reporter`
 ORDER BY `Total` DESC');

As you can see in the statement I am able to sort the columns by 'Partner' alphabetically but what I would like to achieve is sorting the sum of the column net_weight descending. So that the highest values are in the top left decreasing as you go right and down the table.

Can this be done? I have seen examples using WITH ROLLUP but can't seem to get anything to work.

Answer 1

I've edited your example and I used WITH ROLLUP, CASE and FIELD statements to sort and make this:

Information:

mysql> SELECT * FROM test.tblAnnualData;
+----------+---------+------------+------+-----------+
| REPORTER | PARTNER | NET_WEIGHT | YEAR | COMMODITY |
+----------+---------+------------+------+-----------+
| Egypt | Canada | 5 | 2010 | wheat |
| Germany | UK | 1 | 2011 | wheat |
| Mexico | France | 5 | 2011 | wheat |
| Norway | USA | 2 | 2012 | wheat |
| Peru | France | 3 | 2011 | wheat |
| Spain | USA | 3 | 2010 | wheat |
+----------+---------+------------+------+-----------+
6 rows in set (0.00 sec)

Dynamic Query:

SET @@group_concat_max_len = 500000;
SET @QUERY1 = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT(" SUM(CASE WHEN PARTNER = '",PARTNER,"' THEN NET_WEIGHT ELSE 0 END) AS '",PARTNER,"'") 
ORDER BY PARTNER ASC)
INTO @QUERY1 
FROM tblAnnualData;
SET @QUERY1 = CONCAT("SELECT
 REPORTER,
 TOTAL,
 USA,
 France,
 Canada,
 UK
 FROM (SELECT 
 IFNULL(REPORTER,'TOTAL') AS REPORTER,
 SUM(NET_WEIGHT) AS TOTAL,",@QUERY1," FROM tblAnnualData 
 WHERE COMMODITY = 'wheat'
 #AND Year = 2011
 GROUP BY REPORTER WITH ROLLUP) AS A
 ORDER BY FIELD(REPORTER,'TOTAL') DESC,
 TOTAL DESC,
 REPORTER ASC;");
PREPARE QUERY1 FROM @QUERY1;
EXECUTE QUERY1;

It is the same as this query:

SELECT
 REPORTER,
 TOTAL,
 USA,
 France,
 Canada,
 UK
FROM (SELECT 
 IFNULL(REPORTER,'TOTAL') AS REPORTER,
 SUM(NET_WEIGHT) AS TOTAL,
 SUM(CASE WHEN PARTNER='USA' THEN NET_WEIGHT ELSE 0 END) AS USA,
 SUM(CASE WHEN PARTNER='France' THEN NET_WEIGHT ELSE 0 END) AS France,
 SUM(CASE WHEN PARTNER='Canada' THEN NET_WEIGHT ELSE 0 END) AS Canada,
 SUM(CASE WHEN PARTNER='UK' THEN NET_WEIGHT ELSE 0 END) AS UK
 FROM tblAnnualData
 GROUP BY REPORTER WITH ROLLUP) AS A
ORDER BY FIELD(REPORTER,'TOTAL') DESC,
 TOTAL DESC,
 REPORTER ASC;

Why FIELD?

I used FIELD to sort by first when the field is TOTAL (that is the REPORTER aggregated field of the row generated by WITH ROLLUP), then I sort by the TOTAL of NET_WEIGHT. After that I finish with the REPORTER, just in case if some REPORTER has same TOTAL of other/others.

Testing the Dynamic Query:

mysql> SET @@group_concat_max_len = 500000;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @QUERY1 = NULL;
Query OK, 0 rows affected (0.00 sec)
mysql> 
mysql> SELECT GROUP_CONCAT(DISTINCT CONCAT(" SUM(CASE WHEN PARTNER = '",PARTNER,"' THEN NET_WEIGHT ELSE 0 END) AS '",PARTNER,"'") 
 -> ORDER BY PARTNER ASC)
 -> INTO @QUERY1 
 -> FROM tblAnnualData;
Query OK, 1 row affected (0.00 sec)
mysql> 
mysql> SET @QUERY1 = CONCAT("SELECT
 "> REPORTER,
 "> TOTAL,
 "> USA,
 "> France,
 "> Canada,
 "> UK
 "> FROM (SELECT 
 "> IFNULL(REPORTER,'TOTAL') AS REPORTER,
 "> SUM(NET_WEIGHT) AS TOTAL,",@QUERY1," FROM tblAnnualData 
 "> WHERE COMMODITY = 'wheat'
 "> #AND Year = 2011
 "> GROUP BY REPORTER WITH ROLLUP) AS A
 "> ORDER BY FIELD(REPORTER,'TOTAL') DESC,
 "> TOTAL DESC,
 "> REPORTER ASC;");
Query OK, 0 rows affected (0.00 sec)
mysql> PREPARE QUERY1 FROM @QUERY1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Statement prepared
mysql> EXECUTE QUERY1;
+----------+-------+------+--------+--------+------+
| REPORTER | TOTAL | USA | France | Canada | UK |
+----------+-------+------+--------+--------+------+
| TOTAL | 19 | 5 | 8 | 5 | 1 |
| Egypt | 5 | 0 | 0 | 5 | 0 |
| Mexico | 5 | 0 | 5 | 0 | 0 |
| Peru | 3 | 0 | 3 | 0 | 0 |
| Spain | 3 | 3 | 0 | 0 | 0 |
| Norway | 2 | 2 | 0 | 0 | 0 |
| Germany | 1 | 0 | 0 | 0 | 1 |
+----------+-------+------+--------+--------+------+
7 rows in set, 1 warning (0.00 sec)
mysql> 

Try it in SQLFiddle

• Matt Fricker: I am getting an error when I try to go to the SQLFiddle link; it says something went wrong.
• Matt Fricker: On a side note; I can kind of see what you have done, which I have built up myself in SQLFiddle... SQLFiddle however the query does not seem to be sorting by column (USA, France, Canada, UK) in my initial query these were being generated dynamically. I have tried to fiddle with no luck. That's what I essentially want to do is order the columns dynamically, if you can see the fiddle example USA is second last that need to somehow become the first column. If that makes sense.
• oNare: Try with the Dynamic Query: sqlfiddle.com/#!9/0fd97/2
• Matt Fricker: This worked with the simple example above I did need to change the order by column in the GROUP CONCAT stmt and select * columns in the QUERY1 to get the dynamic query to work. sqlfiddle.com/#!9/0fd97/21
• Matt Fricker: In my real world query this didn't order the dynamic columns correctly for some reason. The only way I could get round it was to write another subquery that ordered the columns before they were dynamically generated. You were a big help @oNare, thanks!