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.