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