Pivot tables: analytics in pure SQL

15 April 2:00PM - 2:50PM @ Ballroom E

Experience level: 
50 minutes conference

Rate This Session

Analytics features in MySQL are limited to the aggregation functions, the ones that are used with GROUP BY, i.e. COUNT, SUM, AVG, etc. However, these functions are mono-dimensional, meaning that they group data by a set of properties that produce a list of values. For example, if we run a query for employees, asking the count group by department, broken down by gender, we get a list where every row has two labels (one for department and one for gender) and one number. What is missing is a function that allows you to query data from two dimensions, like the Pivot tables you can get from Excel, where you have the department in the rows and the gender as columns. And of course, also missing are the multi-level multi-dimensional queries, where you have department and gender in the rows, crossing country and locations in columns. You can run these queries in SQL. You just need to know how to create them. This talk shows the basics of creating pivot tables (a.k.a. crosstab queries) in standard SQL, and several tips on how to automate the process.


Quality Assurance Architect, VMware
Quality Assurance Architect at VMware.
Formerly at MySQL AB, and then through acquisitions at Sun Microsystems and Oracle.
Then at Continuent, Inc and currently at VMware through a merge. 
I am active member of the MySQL community and long timer open source enthusiast. During the past decades I have worked in various IT related fields, with focus on databases, object oriented programming, system administration, quality assurance. Fluent in Italian, English, Perl, Python, SQL, Spanish, C, Bash, and good speaker of C , French, Java. I work in cyberspace, with a virtual team.