Just One of the GROUP
The GROUP BY clause lets you consolidate groups of records into a single result. For example, you might combine all the orders for each customer into a single customer order summary. Or you might count the number of customers by country. There's a trap here for the unwary—the term "group" in SELECT has a different meaning than "group" in a report. (In a report, grouping simply refers to layout; it doesn't consolidate.)
When you include GROUP BY in a query, all records whose values exactly match in all fields listed in the GROUP BY clause are consolidated into a single record.
Normally, you use GROUP BY together with a set of functions built into the SELECT command: COUNT(), SUM(), AVG(), MAX() and MIN(). These compute the specified function for the records in a group. You can put either a field name or a more complex expression inside the field. We refer to these as aggregate functions because they compute aggregate results.
All the aggregate functions operate correctly by ignoring null values. So, AVG(SomeField) is really the average of the non-null values of SomeField. This is a welcome change from older versions of FoxPro, which didn't recognize nulls, and brings FoxPro into line with other languages that speak SQL.
The biggest beneficiary of this change may be COUNT(), which accepts "*" as its parameter to give you the number of records in the group. In older versions of FoxPro, it didn't matter whether you put "*" or the name of a field inside COUNT()—the results were the same. In VFP 3 and later, COUNT(SomeField) tells you the number of records in the group with a non-null value for SomeField, while COUNT(*) still gives you the number of records in the group.
If you use one of the aggregate functions without a GROUP BY clause, the result set contains a single record. It's as if you specified a grouping expression that put all the records in a single group.
Once you've done the grouping, you may want to omit some of the groups. The HAVING clause does that for you. It gives you another chance to filter the results, this time looking at intermediate data rather than original data. HAVING accepts the special LIKE, IN and BETWEEN operators, but doesn't accept sub-queries.
Never use HAVING without GROUP BY. If you're not grouping results, you should be able to move the conditions to the WHERE clause, instead. Since WHERE is Rushmore-optimizable and HAVING is not, this can make an enormously significant speed difference. Actually, we have heard of a very few cases where you'd use HAVING without GROUP BY, though we've never run into one ourselves.
