![]() The following statement uses the GROUP BY clause and the SUM() function to find the total inventory by warehouse: SELECT We will use the inventory table that we set up in the GROUPING SETS tutorial for the demonstration. However, MySQL has a slightly different syntax as shown below: SELECTĬode language: SQL (Structured Query Language) ( sql ) SQL ROLLUP examples This syntax is supported by Oracle, Microsoft SQL Server, and PostgreSQL. ![]() () Code language: SQL (Structured Query Language) ( sql ) In the syntax above, ROLLUP(c1,c2) generates three following grouping sets: (c1,c2) This is why we often use ROLLUP to generate the subtotals and the grand total for reporting purposes. For example, if the input column is (c1,c2), the hierarchy c1 > c2. The ROLLUP generates all grouping sets that make sense considering this hierarchy. The ROLLUP assumes a hierarchy among the input columns. The following illustrates the basic syntax of the SQL ROLLUP: SELECTĬode language: SQL (Structured Query Language) ( sql ) Warehouse Code language: SQL (Structured Query Language) ( sql )įor more information about the GROUPING SETS, check it out the grouping sets tutorial. For example, a query that returns the inventory by the warehouse, the grouping set is (warehouse). Note that a grouping set is a set of columns by which you group. By using the ROLLUP option, you can use a single query to generate multiple grouping sets. The ROLLUP option allows you to include extra rows that represent the subtotals, which are commonly referred to as super-aggregate rows, along with the grand total row. The ROLLUP is an extension of the GROUP BY clause. ![]() Summary: in this tutorial, you will learn how to use the SQL ROLLUP to generate multiple grouping sets.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |