Featured Database Articles

MySQL Multi-Aggregated Rows in Crosstab Queries

MySQL's crosstabs contain aggregate functions on two or more fields, presented in a tabular format. In a multi-aggregate crosstab query, two different functions can be applied to the same field or the same function can be applied to multiple fields on the same (row or column) axis. Rob Gravelle shows you how to apply two different functions to the same field in order to create grouping levels in the row axis.

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.

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:

Month

REGION 1

REGION 2

REGION 3

REGION 4

REGION 5

TOTAL

April

13

33

76

2

47

171

May

17

55

209

1

143

425

June

8

63

221

1

127

420

July

13

104

240

6

123

486

August

18

121

274

9

111

533

September

25

160

239

2

88

514

October

9

88

295

2

127

521

November

2

86

292

2

120

502

December

1

128

232

6

155

522

TOTAL

106

838

2078

31

1041

4094

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:

A

B

C1

C2

Total

column header 1

D1

D2

Total

D1

D2

Total

column header 2

E1

E2

Total

E1

E2

Total

E1

E2

Total

E1

E2

Total

column header 3

A1

B1

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

B2

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

Total

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

row sub total

A2

B1

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

B2

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

Total

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

row sub total

Total

---

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

row total

row
header
1

row
header
2

col
sub
total

col
sub
total

col
sub
total

col
sub
total

col
sub
total

col
sub
total

col
total

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:

Year

Month

REGION 1

REGION 2

REGION 3

REGION 4

REGION 5

TOTAL

2004

January

8

41

156

1

42

248

2004

February

1

38

140

0

29

212

2004

March

0

44

115

0

50

209

2004

April

4

45

119

0

42

210

2004

May

1

57

151

0

84

294

2004

June

2

63

142

0

48

259

2004

July

6

47

110

0

33

199

2004

August

10

38

150

1

53

256

2004

September

3

45

146

7

34

235

2004

October

2

55

112

0

34

204

2004

November

4

36

110

1

43

195

2004

December

1

30

165

0

31

227

2004

2004 TOTAL

42

539

1616

10

523

2748

2005

January

3

34

107

2

34

182

2005

February

0

24

103

0

15

144

2005

March

1

30

101

0

24

159

2005

April

3

27

149

0

27

212

2005

May

1

36

110

0

27

177

2005

June

3

52

113

0

28

196

2005

July

1

38

131

3

22

195

2005

August

4

51

146

1

36

238

2005

September

9

52

149

0

49

259

2005

October

10

29

100

0

47

186

2005

November

11

11

162

0

18

204

2005

December

4

66

142

0

19

231

2005

2005 TOTAL

50

450

1513

6

346

2383

2006

January

2

68

132

0

30

235

2006

February

1

43

94

0

24

162

2006

March

3

30

134

0

29

196

2006

April

1

47

129

0

21

199

2006

May

11

52

124

0

31

220

2006

June

9

49

126

0

30

214

2006

July

4

43

125

0

42

217

2006

August

3

50

132

0

51

241

2006

September

6

56

149

0

45

262

2006

October

5

31

121

2

25

184

2006

November

3

42

146

3

63

258

2006

December

0

47

115

0

23

194

2006

2006 TOTAL

48

558

1527

5

414

2582

2007

January

2

37

152

1

35

229

2007

February

1

35

86

2

31

156

2007

March

5

78

132

0

51

268

2007

April

2

41

125

1

41

211

2007

May

0

50

122

0

33

206

2007

June

1

63

107

0

38

209

2007

July

5

41

65

1

31

150

2007

August

0

63

110

2

43

219

2007

September

2

35

134

0

55

227

2007

October

2

39

120

2

52

225

2007

November

10

22

141

0

36

215

2007

December

51

26

60

0

17

156

2007

2007 TOTAL

81

530

1354

9

463

2471

2008

January

1

52

154

1

49

277

2008

February

0

12

84

1

34

136

2008

March

0

30

85

1

27

148

2008

April

3

22

73

0

40

168

2008

May

0

48

90

2

29

170

2008

June

10

63

122

0

24

225

2008

July

19

38

148

3

28

238

2008

August

54

50

105

0

20

230

2008

September

42

34

143

2

44

268

2008

October

73

41

112

1

26

253

2008

November

11

15

101

0

20

152

2008

December

285

62

160

2

29

541

2008

2008 TOTAL

498

467

1377

13

370

2806

2009

January

686

27

123

3

42

882

2009

February

2

9

87

0

22

120

2009

March

4

19

106

0

25

154

2009

April

0

10

95

0

12

117

2009

May

4

31

93

0

21

151

2009

June

63

71

94

0

21

251

2009

July

1

42

92

6

21

165

2009

August

3

53

116

3

19

195

2009

September

1

12

25

0

11

49

2009

December

0

0

1

0

10

17

2009

2009 TOTAL

764

274

832

12

204

2101

GRAND TOTAL

1483

2818

8219

55

2320

15091

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.