MySql View Technique for Grouping Crosstab Column Data

In the database realm, a view is a virtual or logical table,
which contains the results of a SELECT query. Views are generally used to group
related columns from multiple tables together or to hide certain columns. One
of the best features of views is that they behave essentially the same way as a
table in that you can retrieve and update data on them in the same way as with
a table. We can mimic some of this behavior to suit our purposes by replacing
full table aliases with a subquery subset. Hence, a subquery’s resulting view
will contain a limited dataset that has been filtered on the select criteria of
a crosstab column field.

Table Self Aliasing Explained

Sometimes you need to join a table to itself in order to compare
records in a table to other records in that same table. For example, the
following query groups animals by species and by male and female pairs:

SELECT a1.name, 
       a1.sex, 
       a2.name, 
       a2.sex, 
       a1.species
FROM   domestic_animal     AS a1 
INNER JOIN domestic_animal AS a2
ON     a1.species = a2.species 
AND    a1.sex = 'f' 
AND    a2.sex = 'm';

--PRODUCES--:

+--------+------+--------+------+---------+
| name   | sex  | name   | sex  | species |
+--------+------+--------+------+---------+
| Bertha | f    | Bucko  | m    | cow     |
| Buffy  | f    | Killer | m    | dog     |
| Babe   | f    | Bowser | m    | pig     |
+--------+------+--------+------+---------+

Column Fields with Complex Criteria

As we saw in the MySQL
CASE Statements in Multi-Aggregated Crosstab Columns article,
the useage of CASE statements to select column data can lead to a lot of
repetition in the field list, depending on the complexity of the criteria. For
instance, take the following code, which only represents one field:

COUNT(CASE WHEN REGION_CODE      = '01' 
           AND  CITIZENSHIP_CODE = '205' 
           AND  (DOB_DT BETWEEN  '1980-01-01 00:00:00' AND '1990-12-31 23:59:59'
              OR CASE_TYPE_CD    = 'M-10-A')
           THEN FEE_NUMBER END)  AS Cit_1,

Rather than repeat the same criteria for every citizenship column, we
can perform a separate subquery in the FROM clause to obtain all of the
filtered data. The various views can be linked together using JOINs, just like
regular tables. Here again is the query that retrieves the counts of Indian
citizenships and case totals by region code, only this time, the code that
fetches records containing Indian citizenships has been moved to the FROM
clause:

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',        
       Cit_1	 	AS 'Indian Cit for Region 1',
       REGION_1 	AS 'REGION 1',      
       Cit_2	 	AS 'Indian Cit for Region 2',
       REGION_2 	AS 'REGION 2',       
       Cit_3	 	AS 'Indian Cit for Region 3',
       REGION_3 	AS 'REGION 3',      
       Cit_4	 	AS 'Indian Cit for Region 4',
       REGION_4 	AS 'REGION 4',       
       Cit_5	 	AS 'Indian Cit for Region 5',
       REGION_5 	AS 'REGION 5',       
       Cit_TOTAL 	AS 'Indian Cit Total',       
       REGION_TOTAL AS 'REGION TOTAL'
FROM  (SELECT YEAR(ca.CREATION_DATE)                                         AS Year,
              MONTH(ca.CREATION_DATE)                                        AS Month_Num,
              MONTHNAME(ca.CREATION_DATE)                                    AS Month,
              COUNT(CASE WHEN cit.REGION_CODE ='01' THEN cit.FEE_NUMBER END) AS Cit_1,
              COUNT(CASE WHEN ca.REGION_CODE  ='01' THEN ca.FEE_NUMBER  END) AS REGION_1,
              COUNT(CASE WHEN cit.REGION_CODE ='02' THEN cit.FEE_NUMBER END) AS Cit_2,
              COUNT(CASE WHEN ca.REGION_CODE  ='02' THEN ca.FEE_NUMBER  END) AS REGION_2,
              COUNT(CASE WHEN cit.REGION_CODE ='03' THEN cit.FEE_NUMBER END) AS Cit_3,
              COUNT(CASE WHEN ca.REGION_CODE  ='03' THEN ca.FEE_NUMBER  END) AS REGION_3,
              COUNT(CASE WHEN cit.REGION_CODE ='04' THEN cit.FEE_NUMBER END) AS Cit_4,
              COUNT(CASE WHEN ca.REGION_CODE  ='04' THEN ca.FEE_NUMBER  END) AS REGION_4,
              COUNT(CASE WHEN cit.REGION_CODE ='05' THEN cit.FEE_NUMBER END) AS Cit_5,
              COUNT(CASE WHEN ca.REGION_CODE  ='05' THEN ca.FEE_NUMBER  END) AS REGION_5,
              COUNT(cit.fee_number) 		                             AS Cit_TOTAL,
              COUNT(ca.fee_number)  		                             AS REGION_TOTAL
       FROM  TA_CASES as ca
             LEFT JOIN (SELECT * 
                        FROM   TA_CASES
                        WHERE  CITIZENSHIP_CODE ='205') as cit       
             ON (ca.fee_number = cit.fee_number)              
       WHERE ca.CREATION_DATE IS NOT NULL              
       GROUP BY Year, Month_Num WITH ROLLUP) as temp;	

The Left Join

A LEFT
JOIN is used so that all of the records in the left (primary) table are
returned in a MySQL result, whether or not there is a matching record in the
other (dependent) table that it is being joined to. The following diagram
shows two datasets, A and B, where A represents the left table, and B is on the
right of the Join:

Here is
an example that shows the LEFT Join in action. The following two tables
contain music artists and styles:

Using a
Left Join, we can select all of the styles regardless of whether or not there
are any artists in that particular style:

SELECT artists.name       AS artist, 
       styles.description AS style
FROM   styles LEFT JOIN artists ON styles.id = artists.style
ORDER BY styles.description;

The resulting recordset displays NULL for style records that have no
associated artist:

A LEFT
JOIN can also be used to count all of the records in the TA_CASES table, while
still filtering the citizenship columns. (As in the last article, the results
were limited to 1998 and the REGION 4 and 5 data were omitted in order to fit
the table on the page):

The next
stop on our look at crosstab queries is the use of a stored proc to generate
crosstabs where the exact number of columns is not known beforehand.

»


See All Articles by Columnist
Rob Gravelle