Joe Celko s SQL for Smarties - Advanced SQL Programming P48. 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. | 442 CHAPTER 21 AGGREGATE FUNCTIONS Chester A. Arthur R 1881 1885 Grover Cleveland D 1885 1889 Benjamin Harrison R 1889 1893 Grover Cleveland D 1893 1897 William McKinley R 1897 1901 Theodore Roosevelt R 1901 1909 William H. Taft R 1909 1913 Woodrow Wilson D 1913 1921 Warren G. Harding R 1921 1923 Calvin Coolidge R 1923 1929 Herbert C. Hoover R 1929 1933 Franklin D. Roosevelt D 1933 1945 Harry S. Truman D 1945 1953 Dwight D. Eisenhower R 1953 1961 John F. Kennedy D 1961 1963 Lyndon B. Johnson D 1963 1969 Richard M. Nixon R 1969 1974 Gerald R. Ford R 1974 1977 James E. Carter D 1977 1981 Ronald W. Reagan R 1981 1989 George . Bush R 1989 1993 William J. Clinton D 1993 2001 George W. Bush R 2001 NULL Your civics teacher has just asked you to tell her how many people have been President of the United States. So you write the query as select count from Presidents and get the wrong answer. For those of you who have been out of high school too long more than one Adams more than one John and more than one Roosevelt have served as president. Many people have had more than one term in office and Grover Cleveland served two discontinuous terms. In short this database is not a simple one-row one-person system. What you really want is not count but something that is able to look at unique combinations of multiple columns. You cannot do this in one column so you need to construct an expression that is unique. The point is that you need to be very sure that the expression you are using as a parameter is really what you wanted to count. The COUNT ALL value expression returns the number of members in the value expression set. The NULLs were thrown away before the counting took place and an empty set returns zero. The best way to read this is Count the number of known values in this SUM Functions 443 expression with stress on the word known. In this example you might useCOUNT first_name initial last_name . The COUNT DISTINCT value expression returns the number of unique .
