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:
The shooting free movie full online 1967. This is an example of the structure that I am aiming to achieve.
In order to pivot a table in MySQL we must follow 4 steps. Select the columns of interest. Extend the base table with the columns that you picked. Group and aggregate the derived table.
- Jan 29, 2018 A discussion of this topic can be followed on Dynamic Pivot Table Using MySQL. Creating a pivot table using MySQL is a quite easy task, the main obstacle to this process is when we want to sort the data that contain subtotal and total rows generated by WITH ROLLUP clause.
- What is pivoting and how to pivot a table in MySQL. #### Watch the video in fullscreen at 720p for better quality ### Check this article by stratos for more explanation on pivoting a table http.
Here is the query I have produced so far which produces something similar to the above table, however not sorted correctly:
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.
I've edited your example and I used
FIELD statements to sort and make this:
Mysql Pivot Function
It is the same as this query:
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
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:
Try it in SQLFiddleoNareoNare