×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

group by - having count

group by - having count

group by - having count

(OP)
Hi

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

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:

CODE -->

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:

CODE -->

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

RE: group by - having count

(OP)
Hi Mike,

Thanks

Quote:


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.

CODE -->

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

RE: group by - having count

(OP)
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.

Quote:


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

RE: group by - having count

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.

CODE

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:

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close