Joe Celko s SQL for Smarties - Advanced SQL Programming P58. In the SQL database community, Joe Celko is a well-known columnist and purveyor of valuable insights. In Joe Celko's SQL for Smarties: Advanced SQL Programming, he picks up where basic SQL training and experience leaves many database professionals and offers tips, techniques, and explanations that help readers extend their capabilities to top-tier SQL programming. Although Celko denies that the book is about database theory, he nevertheless alludes to theory often to buttress his practical points. This title is not for novices, as the author points out. Instead, its intended. | 542 CHAPTER 23 STATISTICS IN SQL This approach works fine for two variables and would produce a table that could be sent to a report writer program to give a final version. But where are your column and row totals This means you also need to write these two queries SELECT race COUNT FROM Personnel GROUP BY race SELECT sex COUNT FROM Personnel GROUP BY sex However what I wanted was a table with a row for males and a row for females with columns for each of the racial groups just as I drew it. But let us assume that we want to get this information broken down within a third variable such as a job code. I want to see the job_nbr and the total by sex and race within each job code. Our query set starts to get bigger and bigger. A crosstab can also include other summary data such as total or average salary within each cell of the table. Crosstabs by Cross Join A solution proposed by John M. Baird of Datapoint in San Antonio Texas involves creating a matrix table for each variable in the crosstab thus SexMatrix sex Male Female M 1 0 F 0 1 RaceMatrix race asian black caucasian latino Other asian 1 0 0 0 0 black 0 1 0 0 0 caucasian 0 0 1 0 0 latino 0 0 0 1 0 Other 0 0 0 0 1 The query then constructs the cells by using a CROSS JOIN Cartesian product and summation for each one thus Cross Tabulations 543 SELECT job_nbr SUM asian male AS AsianMale SUM asian female AS AsianFemale SUM black male AS BlackMale SUM black female AS BlackFemale SUM cauc male AS CaucMale SUM cauc female AS CaucFemale SUM latino male AS LatinoMale SUM latino female AS LatinoFemale SUM other male AS OtherMale SUM other female AS OtherFemale FROM Personnel SexMatrix RaceMatrix WHERE AND GROUP BY job_nbr Numeric summary data can be obtained from this table. For example the total salary for each cell can be computed by SUM race sex salary AS cell name in place of what we have here. Crosstabs by Outer Joins Another method due to .
