Aggregate Functions (Group By)

Results 1 to 2 of 2

Thread: Aggregate Functions (Group By)

  1. #1
    Genny Guest

    Default Aggregate Functions (Group By)

    I&#039ve run into this problem before and gotten around it with multiple queries but I&#039ve always believed that it should be possible in one query. Here&#039s a simplified version of my query:<BR><BR>SELECT Visitors.VisitorID, COUNT(Visits.VisitID) AS VisitCount<BR>FROM Visitors JOIN Visits ON Visitors.VisitorID = Visits.VisitorID <BR>GROUP BY Visitors.VisitorID<BR><BR>This query works fine. I want to retrieve ADDITIONAL fields from the Visitors table as well, not just the VisitorID field. But if I try to, SQL Server insists that they be part of either an aggregate function or part of the group by statment. It doesn&#039t make sense to put them either place.<BR><BR>Thanks if you can shed light on a good way to handle this all in one query.<BR><BR>-Genny

  2. #2
    Join Date
    Dec 1969

    Default RE: Aggregate Functions (Group By)

    Consider this scenario:<BR><BR>In table Visits:<BR>Record 1 has VisitID=1, VisitorID=1<BR>Record 2 has VisitID=2, VisitorID=2<BR>Record 3 has VisitID=3, VisitorID=1<BR><BR>In table Visitors:<BR>Record 1 has VisitorID=1, VisitorName="John"<BR>Record 2 has VisitorID=1, VisitorName="Harry"<BR>Record 3 has VisitorID=2, VisitorName="Mary"<BR><BR>Do you see the problem? Now *you* know, and I know, that your table does *not* look like that. *You* know that VisitorID is a unique, non-duplicated value. But the SQL engine either doesn&#039t know that or, if it does, isn&#039t smart enough to use the info. So it *assumes* that you might have multiple occurrences of VisitorID=1 and, as a consequence, it can&#039t possibly tell *which* of the various VistorName values it should return from the query!<BR><BR>So, although it may not make *sense* to you to put VisitorNames into the GroupBy statement, you can see that it won&#039t *hurt* to do so (since there aren&#039t any duplicates, the GroupBy won&#039t really have to do any work). And the result is you will make the query engine happy.<BR><BR>I know it seems hacky, but it works well. Just think of it as helping out the dumb query engine.<BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts