Bernie, It may be noteworthy that you never get the duplicates unless you are doing an aggregate action on CreditAmount. The problem may be more related to the CreditAmount column than to the CreditDate column. Actually, COUNT (*) is an aggregate function, but not one that needs a column. Try:
SELECT CreditDate, COUNT (*), COUNT (CreditAmount) FROM Credits + GROUP BY CreditDate (The number will be less that count (*), and will be meaningless, because it will count the number of distinct non-null dollar amounts in creditamounts.) Also, before you do it, SET ZERO OFF SET NULL '-0-' and do your queries again, see if that makes any difference. On Thu, Apr 24, 2008 at 7:44 PM, Bernard Lis <[EMAIL PROTECTED]> wrote: > Hi Bill, > I hope you can discern something from this: > > ----- Original Message ----- > *From:* Bill Downall <[EMAIL PROTECTED]> > *To:* RBASE-L Mailing List <[email protected]> > *Sent:* Thursday, April 24, 2008 8:15 PM > *Subject:* [RBASE-L] - Re: Group by -- need another set of eyes > > Bernie, > > What do you get if you try these: > > SELECT DISTINCT CreditDate FROM Credits > > CreditDate There are lots more going back to 1992 these are the last > few > ---------- > 12/23/2007 > 01/27/2008 > 02/17/2008 > 02/24/2008 > 03/23/2008 > 03/30/2008 > > > SELECT CreditDate, COUNT (*) FROM Credits GROUP BY CreditDate > CreditDate COUNT (*) > ---------- ---------- > 12/23/2007 39 > 01/27/2008 28 > 02/17/2008 25 > 02/24/2008 44 > 03/23/2008 21 > 03/30/2008 33 > > SELECT CreditDate FROM Credits GROUP BY CreditDate > CreditDate > ---------- > 12/23/2007 > 01/27/2008 > 02/17/2008 > 02/24/2008 > 03/23/2008 > 03/30/2008 > > Do you get the doubled dates any of those ways? NO > > Also, just in case all those extra eyes might see something, please show > the results of: > LIST COLUMN CreditDate > list col creditdate > Column Name Table Name Attributes > ------------------ ------------------ > --------------------------------------- > CreditDate Credits Type : > DATE > Index : > SINGLE-COLUMN > Comment: date of > transaction > CRED Type : DATE > > LIST COLUMN CreditAmount > list col creditAmount > Column Name Table Name Attributes > ------------------ ------------------ > --------------------------------------- > CreditAmount Credits Type : CURRENCY NOT > NULL > Comment: dollar amount of > transaction > CRED Type : > CURRENCY > > LIST TABLE Credits > > list table credits > > Table: Credits No Lock(s) > Descr: Credit transactions > > No. Column Name Attributes > --- ------------------ > ------------------------------------------------------ > 1 CreditRefNo Type : INTEGER NOT NULL > AUTONUMBER > Consrnt: PRIMARY > KEY > Comment: Reference > no > 2 OrderNo Type : > INTEGER > Consrnt: FOREIGN KEY REFERENCES > OrderHeader > Comment: order > number > 3 CustomerNo Type : > INTEGER > Consrnt: FOREIGN KEY REFERENCES > Customer > Comment: customer > number > 4 CreditDate Type : > DATE > Index : > SINGLE-COLUMN > Comment: date of > transaction > 5 AccountNo Type : TEXT > 4 > Comment: account > number > 6 CreditAmount Type : CURRENCY NOT > NULL > Comment: dollar amount of > transaction > 7 CreditComment Type : TEXT > 30 > Comment: > comment > 8 CreditUser Type : TEXT > 18 > Comment: last modified > by > 9 CreditDateTime Type : > DATETIME > Comment: date/time last > modified > Current number of rows: 9420 > > SHOW DATE > SHOW ZERO > SHOW NULL > > R>show date > DATE format mm/dd/yyyy > DATE sequence MMDDYY > Century threshold YEAR is 50 > Default CENTURY is 19 > > R>show zero > (ZERO ) ON Treat nulls as ZERO in numeric calculations > > R>show null > NULL symbol > > Bill > > > > On Thu, Apr 24, 2008 at 4:56 PM, Bernard Lis <[EMAIL PROTECTED]> wrote: > > > Right on John, but for the record CreditDate is not indexed. > > ----- Original Message ----- From: "John Engwer" <[EMAIL PROTECTED]> > > To: "RBASE-L Mailing List" <[email protected]> > > Sent: Thursday, April 24, 2008 10:23 AM > > Subject: [RBASE-L] - Re: Group by -- need another set of eyes > > > > > > Larry, he created a temp table and ran the select from the temp table. > > > That > > > should have eliminated the index possibility. > > > > > > John > > > > > > -----Original Message----- > > > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of > > > Lawrence > > > Lustig > > > Sent: Thursday, April 24, 2008 9:56 AM > > > To: RBASE-L Mailing List > > > Subject: [RBASE-L] - Re: Group by -- need another set of eyes > > > > > > Bernie: > > > > > > I'll bet dollars to donuts this is an indexing issue. > > > > > > Is CreditDate indexed? If so, try dropping the index and repeating > > > the > > > command (which will run more slowly). In fact, try projecting a > > > completely > > > unindexed copy of the table and operating on that. > > > > > > Also, look for CreditDate used in a multi-column index. > > > > > > -- > > > Larry > > > > > > > > > > > > > > > > >
