group by - having count 1

[mjcmkrsr]

Hi

Strange behavior of SELECT ... GROUP BY ... HAVING ...

SELECT cName from stagiaires GROUP BY cName HAVING COUNT(cName) > 1 &&& works
SELECT SUBSTR(cName,1,15) as cTrunkName from stagiaires GROUP BY cTrunkName HAVING COUNT(cTrunkName) > 1 &&& yields an error
SELECT SUBSTR(cName,1,15) as cTrunkName from stagiaires GROUP BY cTrunkName HAVING COUNT("cTrunkName") > 1 &&& works

Is there an explanation why you have to put "" around the Alias Name in HAVING COUNT(...)
Thanks
MarK

 

[Mike Lewis]

Mark,

I've seen the same thing. I assume it is just the way the COUNT() function works. It has nothing to do with the HAVING. The following would give the same error:

SELECT SUBSTR(cName,1,15) as cTrunkName, count(cTrunkname) from stagiaires GROUP BY cTrunkName

However, you can use the column number rather than the column name, so this should work as well:

SELECT SUBSTR(cName,1,15) as cTrunkName from stagiaires GROUP BY cTrunkName HAVING COUNT(1) > 1

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi Mike,

Thanks

SELECT SUBSTR(cName,1,15) as cTrunkName from stagiaires GROUP BY cTrunkName HAVING COUNT(1) > 1

Maybe, but it gets even stranger - all the code below yields the same result. COUNT does not seem to refer to any column.

SELECT SUBSTR(cName,1,15) as cTrunkName from stagiaires GROUP BY cTrunkName HAVING COUNT("cTrunkName") > 1
SELECT SUBSTR(cName,1,15) as cTrunkName from stagiaires GROUP BY cTrunkName HAVING COUNT(1) > 1
SELECT SUBSTR(cName,1,15) as cTrunkName from stagiaires GROUP BY cTrunkName HAVING COUNT(2) > 1
SELECT SUBSTR(cName,1,15) as cTrunkName from stagiaires GROUP BY cTrunkName HAVING COUNT(3) > 1
SELECT SUBSTR(cName,1,15) as cTrunkName from stagiaires GROUP BY cTrunkName HAVING COUNT(9) > 1
SELECT SUBSTR(cName,1,15) as cTrunkName from stagiaires GROUP BY cTrunkName HAVING COUNT(0) > 1
SELECT SUBSTR(cName,1,15) as cTrunkName from stagiaires GROUP BY cTrunkName HAVING COUNT("X") > 1
SELECT SUBSTR(cName,1,15) as cTrunkName from stagiaires GROUP BY cTrunkName HAVING COUNT("") > 1

MarK

 

[mjcmkrsr]

Hi Mike,

I checked in Hacker's Guide to VFP 7. Here what it says - and it makes sense. However no explanation why COUNT() requires " " around the alias field name.

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.

hth
MarK

 

[Olaf Doschke]

Well, Count() also works normal on single fields, also on expressions, just not on names of computed columns, but that's also not standard SQL, AFAIK.

T-SQL allows to work more with expressions than VFP allows, for example, GROUP BY expression. We can GROUP BY column number, but that's not working if that column is an expression/aggregate.

So all in all, different SQL dialects = different rules and capabilities.

Open Database (AddBS(_samples)+"Northwind\Northwind.dbc")
Select Country, Count(*) as CustomerCountofCountry, Count(Region) as CountryCustomersWithRegionInfo, Count(Nvl(Region,"")) as ReferenceCountperCountry from customers group by Country

You can (since VFP9) always make a query an inner query with an alias and query from that as if it was a table. THEN you can reference field names of the computed fields and Count/Sum/Group by them.

For example:

Select Count(*), Sum(RegionCount), Avg(RegionCount) as AverageRegionCount, Avg(Evl(RegionCount,Cast(.null. as int))) as RealAverageRegionCount From;
(Select Country, Count(Region) as RegionCount from customers group by Country) Countries

Because after the inner query ran it's computed columns become real column names you can address normally.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name