SORTING, GROUPING, COMMENTS

Sorting Table Data

Sorting is one of the most commonly used data management tools. You may sort your table in Excel by one or more columns, in ascending or descending order, or by a custom sort.

Organize the table

1. Choose a cell from the data.
2. Go to Home > Sort & Filter.

Alternatively, go to Data > Sort.

3. Choose one of the following:

• Sort A to Z – ascending order of the specified column.
• Sort Z to A – arranges the columns in decreasing order.
• Custom Sort – arranges data in multiple columns using various sort criteria.

Here’s how to make your own sort:

• Choose a Custom Sort.
• Choose Add Level.
• Select the column you want to sort by from the drop-down menu, and then select the second column you want to sort by. Sort by department, then status.
• Select Values for Sort On.
• Choose a choice for order, such as A to Z, Smallest to Largest, or Largest to Smallest.
• Repeat steps 2-5 for each additional column you want to sort by.
• If your data has a header row, choose the My data has headers checkbox.
• Choose OK.

Grouping Table Data and Roll-Up in MySQL

In MySQL, the ROLLUP modifier is used to generate the summary output, which includes additional rows that indicate super-aggregate (higher-level) summary processes. It allows us to summarize the results of numerous layers of investigation with a single query. Its primary purpose is to facilitate OLAP (Online Analytical Processing) processes.

In MySQL, the ROLLUP modifier can only be used with the GROUP BY query.

Syntax:

The syntax for using the rollup modifier is as follows:

• SELECT
• FROM 

table name, column 1, column 2, column 3,…

• WITH ROLLUP, GROUP BY column1, column2,…

The column names presented in the query result must be specified in the SELECT clause in this format. The table’s name will be mentioned next. Following that, we supplied the GROUP BY clause, which included the column names for which we wanted to aggregate data. Finally, we use the WITH ROLLUP modifier to display the super-aggregate result in a new row.

We’ve already seen how the GROUP BY query is used in conjunction with aggregate functions like MAX, MIN, SUM, COUNT, AVG, and others to group the output rows by single or many columns. The ROLLUP modifier is a GROUP BY query option that adds extra fields for expressing subtotals. These additional rows are known as super-aggregate rows,” and they are made up of the grand-total rows. Thus, using the ROLLUP modifier, we may generate numerous groupings of set rows within a single query depending on the columns supplied in MySQL’s GROUP BY clause.

Explanation of MySQL Rollup

To understand the rollup modifier, we must first grasp what a grouping set is. A grouping set is a collection of columns that we wish to group together to get the desired output.

The NULL column is visible. It is because the UNION ALL clause requires all queries to have the same number of columns. To meet this criterion, we added NULL to the second query’s select list.

The NULL in the Year column output signifies the grand-total super-aggregate value. This query has two issues since it can yield total sales for each year as well as grand-total aggregate sales:

• It lengthens the query considerably.
• It reduces query performance since the database engine conducts two distinct queries internally and merges the returned sets into a single output.

To address these concerns, MySQL introduces the ROLLUP clause, which allows us to do two levels of analysis in a single query. The ROLLUP clause is a GROUP BY extension that generates another row and displays the grand-total (super-aggregate) values.

Comments in MySQL

The MySQL Server supports three types of comments:

1. From a # character until the line’s conclusion.
2. From a sequence to the end of the line. The (double-dash) comment style in MySQL requires at least one whitespace or control character after the second dash (such as a space, tab, newline, and so on). This syntax differs somewhat from standard SQL comment syntax when used as the Start of a Comment.
3. As in the C programming language, from one sequence to the next sequence. Because the opening and ending sequences do not have to be on the same line, this syntax allows a comment to span many lines.

• Nested comments are not supported and should be avoided; they will be deleted in a future MySQL version. (Nestled comments may be tolerated in some instances, but they are normally not, and users should avoid them.)
• Certain C-style comment versions are supported by MySQL Server. By utilizing comments of the following kind, you may develop code that includes MySQL extensions while being portable:
  MySQL-specific code

• The syntax mentioned above pertains to how the MySQL server parses SQL queries. Before transmitting statements to the server, the MySQL client software does some processing. (It does this to establish the borders of statements inside a multiple-statement input line.)