Skip navigation links

MySQL Newsletter - Subscribe Today!

MySQL Wizardry

By Giuseppe Maxia
05 June 2001
JUMP TO:
> Introduction
> A call for help - Defining the problem
> Opening the path
> Some help from SQL itself
> The golden rules
> Conclusion
> Printable Version

Introduction

Cross tabulations are statistical reports where you de-normalize your data and show results grouped by one field, having one column for each distinct value of a second field.

Basic problem definition. Starting from a list of values, we want to group them by field A and create a column for each distinct value of field B.

  

The desired result is a table with one column for field A, several columns for each value of field B, and a total column.

According to some authoritative sources (Joe Celko, "SQL for smarties") we should use specialized (and expensive) statistical tools to achieve this purpose with a database server.

My recent experience with MySQL has shown that you don't have to invest a fortune to have server-side cross-tabulations. The way I found it is littered with errors and disappointment, and in perspective it should appear quite boring. This is the chronicle of how I would have liked to find out a solution.

Next: A call for help - Defining the problem

DevShed.com is the independent Open Source Web Development Site. Fresh tutorials, articles and discussion of MySQL, PHP, Perl, Python, Apache, JSP and administration can be found daily at http://www.DevShed.com/ This article is Copyright 2001 by Developer Shed, Inc. All rights reserved. Reproduced with permission.