group by - having count
group by - having count
(OP)
Hi
Strange behavior of SELECT ... GROUP BY ... HAVING ...
Is there an explanation why you have to put "" around the Alias Name in HAVING COUNT(...)
Thanks
MarK
Strange behavior of SELECT ... GROUP BY ... HAVING ...
CODE -->
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
RE: group by - having count
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:
CODE -->
However, you can use the column number rather than the column name, so this should work as well:
CODE -->
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro articles, tips and downloads
RE: group by - having count
Thanks
Maybe, but it gets even stranger - all the code below yields the same result. COUNT does not seem to refer to any column.
CODE -->
MarK
RE: group by - having count
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.
hth
MarK
RE: group by - having count
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.
CODE
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:
CODE
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