The trick to this type of query is the transformation of rows of data to columns.That presents the data in a table format in which one field is displayed down the left side of a matrix (row headings) and the other across the top (column headings).
Aggregating on periods of less than a year presents an extra challenge because we have to be careful to count each period as a distinct entity, e.g., January of 2006s data must not include that of any other year. Mysql Pivot With Unknown Number Of Columns Movie Rentals ForIn todays article, well create a crosstab query for the Sakila sample database that counts the number of movie rentals for each customer aggregated by month. ![]() Mysql Pivot With Unknown Number Of Columns Full Schema StructureThe full schema structure can be viewed on the MySQL Dev site. These will display the customer names, along with their yearly and monthly video rental totals. The last column on the right will contain totals for each year, while the last row will provide grand totals for each month. Finally, the cell in the bottom-right will display the total number of movies rented. There will always be twelve of them and in exactly the same order. Compare those to the customers, which can change between query executions. Whenever you have one known quantity and one unknown, choosing the rows and columns is a no-brainer the known quantity must take the columns. The with rollup clause produces an extra row at the end of each year and customer. ![]() In addition to the grouped column value, there are also duplicated months. This happens because MySQL permits a column that does not appear in the GROUP BY list to be named in the select list. Mysql Pivot With Unknown Number Of Columns Free To ChooseThe server is free to choose any value from this non-aggregated column in summary rows, and this includes the extra rows added by WITH ROLLUP. In this case, the month name is a non-aggregated column, even though the month index is. Substituting the month() function for monthname() in the select list would replace the duplicated month values with nulls. To do that, we can test for the month index and only count that row if its that columns month. These were produced by placing the above statement within a subquery and selecting from it. A Null value signifies an aggregate row and is populated with a Total label. The only time that you may want to consider assigning them to rows is if the related field is also fixed. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |