Re: help with group by

Forgive the soapbox speech, but I have two stylistic suggestions...

First of all, COUNT(CHILDREN) is syntactically acceptable, but a bit misleading. When you use COUNT here, you may want to count 1 of 2 things:
1) The number of rows for each FULLNAME in the table, or 2) The number of distinct children there are associated with a given FULLNAME. Let's say 'Smith, John' has three rows in the table - one row with children = 'Bob', and two rows with children = 'Alice'. You probably want the count of children for 'Smith, John' to be 2. Based on what you have coded...COUNT(CHILDREN)... a person (yourself included) may *think* you will get the result of 2, but you're actually going to get 3. To avoid this type of confusion, you should get in the habit of either:
1) use COUNT(*) if you want the total number of rows or 2) use COUNT(DISTINCT <column_name>) if you want the number of distinct occurences of that column name.

You should never just COUNT(<column_name>), because the returned value will have *nothing* to do with <column_name>. Even if you know what you are doing, the next person may not.

Suggestion #2
I'll probably take some flack for this, but I think that in the interest of avoiding redundancy (having to put l_name || ',' || f_name in two places) and ensuring correctness (select f(col1, col2) may not ALWAYS have the same grouping as group by col1, col2), I would strongly recommend a slightly more verbose solution:

SELECT FULLNAME, COUNT(DISTINCT CHILDRENT) FROM (
SELECT L_NAME || ',' || F_NAME FULLNAME, CHILDREN
FROM PEOPLE)
GROUP BY FULLNAME Consider - if you ever wanted to add a middle initial to the query, this style is the only one where you need only make a change in one place...

(PS - does anyone know how to add whitespace/indentation to the messages out here???) Received on Wed Jan 12 2005 - 12:11:38 CST