Path: utzoo!utgpu!news-server.csri.toronto.edu!rutgers!ucsd!ucbvax!ucdavis!csusac!unify!ufycorp!meh From: meh@ufycorp.Unify.Com (Mark Hansen) Newsgroups: comp.databases Subject: Re: Problem with SQL join/group-by in Unify database (long) Message-ID: Date: 22 Jan 91 03:36:01 GMT References: <584@fciva.FRANKCAP.COM> Sender: news@Unify.Com (news admin) Distribution: na Organization: /usr/lib/news/organization Lines: 51 In article <584@fciva.FRANKCAP.COM> dag@fciva.UUCP (Daniel A. Graifer) writes: >Let me appologize in advance for the lengthly example. >I am having a problem with group-by with joins in Unify/Accell SQL: > >$ SQL -version >4.0-880916 SYS5LCK USSLCK M8BIT (international) >sql> select rd_co_id, rd_co_name, count(*), sum(ap_loan_amt) from apmaster, >sql> rolodex where ap_funding_date between 12/01/90 and 12/31/90 >sql> and ap_region = 2 and rd_co_id = ap_broker >sql> group by rd_co_name / >recognized query! > [Detail of querries and results removed ...] >Any Ideas? Anxiously awaiting any input, and thanks in advance... >Dan The one basic problem that I see with your queries off hand, is the fact that you are creating an aggregate query, but not all the projected columns are aggregates. For example, in the above query you select rd_co_id, rd_co_name, count(*), and sum( ap_loan_amt ) from apmaster grouped by the rd_co_name column. The count(*) and sum( ap_loan_amt ) are aggregates for obvious reasons. The rd_co_name is an aggregate because it is in the 'group by' clause. The remaining column, rd_co_id, is not an aggregate, however, and is what is causing your problem. The SQL manual, on page 16-26, goes into this a little, and although doesn't come right out and say it, tells you that you can't have any projected columns that are not aggregates in an aggregate query. The actual BUG is that the SQL doesn't come back and tell you that this is a syntax error. In later versions, it does. Hope this is helpful. >-- >Daniel A. Graifer Coastal Capital Funding Corp. >Sr. Vice President, Financial Systems 7900 Westpark Dr. Suite A-130 >(703)821-3244 McLean, VA 22102 >uunet!fciva!dag fciva.FRANKCAP.COM!dag@uunet.uu.net Mark E. Hansen internet: meh@Unify.Com Manager, Client Support Services ...!{csusac,pyramid}!unify!meh Unify Corporation voice: (916) 922-1177 3870 Rosin Court, Sacramento, CA 95834 fax: (916) 920-5306