MySQL Multi-Aggregated Rows in Crosstab Queries

Today’s topic of discussion is crosstabs, which contain multiple aggregate functions in the row axis of a tabular resultset. Recall from the the All About the Crosstab Query article that an aggregate function is one that summarizes a group of related data in some way. Examples of aggregate functions include COUNT, SUM, AVG, MIN, and MAX. In a multi-aggregate crosstab query, two different functions can be applied to the same field or the same function can be applied to two or more fields. Today we'll create a query that applies two different functions to the same field in order to create grouping levels in the row axis.

Recap of Crosstab Basics

In the Tips for Simplifying Crosstab Query Statements article, we took the complex SQL statement that we created in the All About the Crosstab Query article and simplified it to a more streamlined version:

SELECT CASE WHEN Month_Num IS NULL 
            THEN 'TOTAL' 
            ELSE Month 
       END      AS 'Month',
  REGION_1 AS 'REGION 1',  
  REGION_2 AS 'REGION 2',
  REGION_3 AS 'REGION 3',
  REGION_4 AS 'REGION 4',
  REGION_5 AS 'REGION 5',       
  TOTAL
FROM  (SELECT MONTH(CREATION_DATE)                          AS Month_Num,
     MONTHNAME(CREATION_DATE)                               AS Month,
     COUNT(CASE WHEN REGION_CODE ='01' THEN FEE_NUMBER END) AS REGION_1,
     COUNT(CASE WHEN REGION_CODE ='02' THEN FEE_NUMBER END) AS REGION_2,
     COUNT(CASE WHEN REGION_CODE ='03' THEN FEE_NUMBER END) AS REGION_3,
     COUNT(CASE WHEN REGION_CODE ='04' THEN FEE_NUMBER END) AS REGION_4,
     COUNT(CASE WHEN REGION_CODE ='05' THEN FEE_NUMBER END) AS REGION_5,
     COUNT(*)                                               AS TOTAL 
  FROM   TA_CASES
  WHERE  YEAR(CREATION_DATE)=1998
  GROUP BY Month_Num WITH ROLLUP) AS CA;

The subquery fetched all of the fields that we needed, plus the month number, for sorting.  We then selected from it by placing the code after the FROM of a second query. Performing a query in a two pass process in this way is called Pre (or Partial)-Aggregation. The first pass creates a derived table or resultset that performs most of the aggregation work, while the next pass does some formatting and any additional calculations that may be required.  The neccessitating of pre-aggregation here was the result of two factors: The ROLLUP GROUP BY modifier inserted a Null row into the resultset, which was difficult to replace with the “TOTAL” row header because of its late evaluation in the query process.  A second challenge was presented by the grouping on the output of date functions because grouping on the MONTHNAME() sorted the rows in alphabetical order, rather than chronological. 

The above query produced the following desired crosstab, including chronological row sorting and totals:

Multi-Aggregate Pivots

This query was not terribly complex as it only hit one table and pivoted between two fields: the Month and Region.  That’s called a Single Pivot.  If we wanted to breakdown the time periods further into other time periods, we would now be looking at a two-to-one pivot (eg: months and weeks per region).  On the column side, regions could likewise be broken down into specific cities, giving us a one-to-two pivot.  Adding the new fields to both the columns and rows would produce a true many-to-many multi-aggregate pivot.  The following chart illustrates the inherent complexety of multi-aggregate pivots:

Reporting on Two Row Fields

We’re going to create a Multi-Aggregate row crosstab by adding the Year to the row data, making it the new A row in the above diagram, thus displacing the months to the B field. That will allow us to report on multiple years, by calling the YEAR() function on the CREATION_DATE, much like we did for displaying the months. We’ll insert it as the first field in the SELECT list (new code appears in Red):

...
FROM  (SELECT YEAR(CREATION_DATE)              AS Year,
              MONTH(CREATION_DATE)             AS Month_Num,
              MONTHNAME(CREATION_DATE)         AS Month,
		 ...

We can also easily include totals for each year by adding it to the GROUP BY clause:

       GROUP BY Year, Month_Num WITH ROLLUP) AS CA;

In addition to needing a column heading for the years, we also need to alter the months CASE statement, because rows which display the yearly totals will contain a NULL Month_num value:

SELECT CASE WHEN Year IS NULL
            THEN 'GRAND TOTAL' 
            ELSE Year 
       END      AS 'Year',
       CASE WHEN Month_Num IS NULL     
            THEN CASE WHEN Year IS NULL
                      THEN ''                                         
                      ELSE CONCAT(Year, ' TOTAL')                      
                 END
            ELSE Month 
       END      AS 'Month',  
	 ...

Here then is the full SQL statement to include the Year rows.

SELECT CASE WHEN Year IS NULL
            THEN 'GRAND TOTAL' 
            ELSE Year 
       END      AS 'Year',
       CASE WHEN Month_Num IS NULL     
            THEN CASE WHEN Year IS NULL
                      THEN ''                                         
                      ELSE CONCAT(Year, ' TOTAL')                      
                 END
            ELSE Month 
       END      AS 'Month',  
       REGION_1 AS 'REGION 1',  
       REGION_2 AS 'REGION 2',
       REGION_3 AS 'REGION 3',
       REGION_4 AS 'REGION 4',
       REGION_5 AS 'REGION 5',       
       TOTAL
FROM  (SELECT YEAR(CREATION_DATE)                                    AS Year,
              MONTH(CREATION_DATE)                                   AS Month_Num,
              CONVERT(MONTHNAME(CREATION_DATE) USING latin1)         AS Month,
              COUNT(CASE WHEN REGION_CODE ='01' THEN FEE_NUMBER END) AS REGION_1,
              COUNT(CASE WHEN REGION_CODE ='02' THEN FEE_NUMBER END) AS REGION_2,
              COUNT(CASE WHEN REGION_CODE ='03' THEN FEE_NUMBER END) AS REGION_3,
              COUNT(CASE WHEN REGION_CODE ='04' THEN FEE_NUMBER END) AS REGION_4,
              COUNT(CASE WHEN REGION_CODE ='05' THEN FEE_NUMBER END) AS REGION_5,
              COUNT(*)                                               AS TOTAL 
       FROM   TA_CASES
       WHERE  YEAR(CREATION_DATE)>2003
       GROUP BY Year, Month_Num WITH ROLLUP) AS CA;

The revised SQL code produces the following result, which includes the YEAR headers in the first column, and the TOTAL summary row for each year:

Adding row fields is not all that difficult because queries naturally group data by rows. Therefore, all that is required is to add the new row to the SELECT and GROUP BY field lists and include a column for the row headers, as we did above. In the next installment, we will take on the more imposing challenge of adding a new column. It’s not as straighforward as might initially appear. 

Additional Resources

MySQL

» See All Articles by Columnist Rob Gravelle