Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted Mar 12, 2010

MySql View Technique for Grouping Crosstab Column Data

By Rob Gravelle

Learn a technique to fetch column data for MySQL crosstabs by emulating views. By replacing tables with a subquery subset, the resulting view will contain a limited dataset that has been filtered for that column field.

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:

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:

styles

id

description

1

Pop

2

Easy Listening

3

Classic Rock

4

Heavy Metal

5

Soul

6

Seventies

7

Eighties

8

Hip Hop

9

Jazz

10

Guitar Bands

artists

id

name

style

1

Michael Jackson

1

2

The Beatles

3

3

Eminem

8

4

Rob Gravelle

10

5

Annihilator

4

6

Exciter

4

7

Bon Jovi

7


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:

Query Results

artist

style

The Beatles

Classic Rock

(NULL)

Easy Listening

Bon Jovi

Eighties

Rob Gravelle

Guitar Bands

Annihilator

Heavy Metal

Exciter

Heavy Metal

Eminem

Hip Hop

(NULL)

Jazz

Michael Jackson

Pop

(NULL)

Seventies

(NULL)

Soul

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):

Year

Month

Indian Cit for Region 1

REGION 1

Indian Cit for Region 2

REGION 2

Indian Cit for Region 3

REGION 3

Indian Cit Total

REGION TOTAL

1998

April

0

13

1

33

1

76

2

171

1998

May

0

17

2

55

9

209

11

425

1998

June

0

8

0

63

7

221

10

420

1998

July

0

11

2

104

12

240

17

484

1998

August

0

18

0

121

4

274

7

533

1998

September

0

25

1

160

1

239

7

514

1998

October

0

9

0

88

6

295

10

521

1998

November

0

2

0

86

3

292

3

502

1998

December

0

1

2

128

3

232

5

522

1998

1998 TOTAL

0

104

8

838

46

2078

72

4092

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



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM