Joe Celko s SQL for Smarties - Advanced SQL Programming P55. 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. | 512 CHAPTER 23 STATISTICS IN SQL This would return the result set red green for the example table and would not change to green until the ratio of red to green tipped by two percentage points. Likewise you can use a derived table to get the mode. WITH SELECT salary COUNT FROM Payroll GROUP BY salary AS P1 salary occurs SELECT salary FROM P1 WHERE SELECT MAX occurs IN P1 This is probably the best approach since the WITH clause will materialize the P1 table and can locate the max while doing so. The AVG Function One problem is that SQLs likes to maintain the data types so if x is an INTEGER you may get an integer result. You can avoid this by writing AVG x or AVG CAST x as float or AVG CAST x as decimal s p to be safe. This is implementation-defined so check your product first. Newbies tend to forget that the built-in aggregate functions drop the rows with NULLs before doing the computations. This means that sum x count is not the same as avg x . Consider x count versus avg coalesce x as versions of the mean that handle NULLs differently. Sample and population means are slightly different. A sample needs to use frequencies to adjust the estimate of the mean. The formula SUM x abs_perc 100 AS mean_p needs the VIEW we had at the start of this section. The name mean_p is to remind us that it is a population mean and not the simple avg of the sample data in the table. The Median The median is defined as the value for which there are just as many cases with a value below it as above it. If such a value exists in the data set this value is called the statistical median by some authors. If no such value exists in the data set the usual method is to divide the data set into two The Median 513 halves of equal size such that all values in one half are lower than any value in the other half. The median is then the average of the highest value in the lower half and the lowest value in the upper half and is called the financial median
