Joe Celko s SQL for Smarties - Advanced SQL Programming P29. 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 audience. | 252 CHAPTER 11 CASE EXPRESSIONS CASE WHEN value exp 1 value exp 2 THEN NULL ELSE value exp 1 END CASE Expressions with GROUP BY A CASE expression is very useful with a GROUP BY query. For example to determine how many employees of each gender by department you have in your Personnel table you can write SELECT dept_nbr SUM CASE WHEN gender M THEN 1 ELSE 0 AS males SUM CASE WHEN gender F THEN 1 ELSE 0 AS females FROM Personnel GROUP BY dept_nbr or SELECT dept_nbr COUNT CASE WHEN gender M THEN 1 ELSE NULL AS males COUNT CASE WHEN gender F THEN 1 ELSE NULL AS females FROM Personnel GROUP BY dept_nbr I am not sure if there is any general rule as to which form will run faster. Aggregate functions remove nulls before they perform their operations so the order of execution might be different in the else 0 and the else null versions. The previous example shows the case expression inside the aggregate function it is possible to put aggregate functions inside a case expression. For example assume you are given a table of employees skills CREATE TABLE PersonnelSkills emp_id CHAR 11 NOT NULL skill_id CHAR 11 NOT NULL primary_skill_ind CHAR 1 NOT NULL CONSTRAINT primary_skill_given CHECK primary_skill_ind IN Y N PRIMARY KEY emp_id skill_id The CASE Expression 253 Each employee has a row in the table for each of his skills. If the employee has multiple skills she will have multiple rows in the table and the primary skill indicator will be a Y for her main skill. If she only has one skill which means one row in the table the value of primary_skill_ind is indeterminate. The problem is to list each employee once along with her only skill if she only has one row in the table or her primary skill if she has multiple rows in the table. SELECT emp_id CASE WHEN COUNT 1 THEN MAX skill_id ELSE MAX CASE WHEN primary_skill_ind Y THEN skill_id END ELSE NULL END END AS main_skill FROM PersonnelSkills GROUP BY emp_id This solution looks at first like a violation of the rule in SQL .
