Bernie,
There should only be two rows on tCREDITS. How many rows did you get? Is the CREDIT table a temp table also? John From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Bernard Lis Sent: Wednesday, April 23, 2008 4:06 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Group by -- need another set of eyes Hi John, I created the temp table, as you suggested and -- >From the Temp table, in Edit mode Calculate > Compute: creditamount Count = 54 Rows = 9420 Minimum = 0.01 Maximum = 1,324.00 Sum = 5,771.35 Average = 106.88 Std Dev = 219.81 Variance = 48,314.24 The Sum is the correct amount Still Gremlins: sel creditdate,creditamount=s from tcredits creditdate creditamount ---------- --------------- 03/23/2008 2,952.16 03/30/2008 2,819.19 03/23/2008 2,952.16 03/30/2008 2,819.19 ---------- --------------- 11,542.70 ----- Original Message ----- From: John Engwer <mailto:[EMAIL PROTECTED]> To: RBASE-L Mailing List <mailto:[email protected]> Sent: Wednesday, April 23, 2008 3:38 PM Subject: [RBASE-L] - RE: Group by -- need another set of eyes Bernie, have you examined the data via the edit command "edit creditdate,creditamount from credits whe Creditdate between 3/1/2008 and 3/31/2008 Find the aggregate of creditamount and see if it equals the amount that you are expecting. If the data is correct then project a temp table with zero rows. Project temporary tCREDITS from credits using creditdate,creditamount whe limit = 0 Then insert rows from CERDITS via the select and group by option. INSERT INTO tCREDITS (creditdate,creditamount) + SELECT creditdate,creditamount FROM CREDITS + where Creditdate between 3/1/2008 and 3/31/2008 + group by CreditDate The results in tCREDITS should be exactly what you want. From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Bernard Lis Sent: Wednesday, April 23, 2008 3:11 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Group by -- need another set of eyes I did a reload, that also packed the indexes. This is driving me crazy. ----- Original Message ----- From: [EMAIL PROTECTED] To: RBASE-L Mailing List <mailto:[email protected]> Sent: Wednesday, April 23, 2008 2:24 PM Subject: [RBASE-L] - RE: Group by -- need another set of eyes Bernie Try packing the indexes for that table. Dawn Hast Office - (828) 646-2669 Cell - (828) 246-8543 "Bernard Lis" <[EMAIL PROTECTED]> wrote on 04/23/2008 01:20:37 PM: > Hi Emmitt, > They are basically the same only diff is the name, I merely changed the word > credit for debit whereever it appears > i.e. CreditDate, DebitDate CreditAmount, DebitAmount, etc. > These tables have been around for at least 10-15 years without any change to > structure. > > Bernie Lis > > ----- Original Message ----- > From: "Emmitt Dove" <[EMAIL PROTECTED]> > To: "RBASE-L Mailing List" <[email protected]> > Sent: Wednesday, April 23, 2008 1:10 PM > Subject: [RBASE-L] - RE: Group by -- need another set of eyes > > > > Bernie, > > > > What are the structural differences between the two tables (credits and > > debits)? > > > > Does the credit table have a key on CreditDate? Is CreditDate also > > indexed? > > > > Emmitt Dove > > Manager, DairyPak Business Systems > > Evergreen Packaging, Inc. > > [EMAIL PROTECTED] > > [EMAIL PROTECTED] > > (203) 643-8022 > > > > > > -----Original Message----- > > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Bernard > > Lis > > Sent: Wednesday, April 23, 2008 12:57 PM > > To: RBASE-L Mailing List > > Subject: [RBASE-L] - RE: Group by -- need another set of eyes > > > > Here's a fine How-do-you-do: > > If I leave off the group by, I get the correct total: > > > > sel sum(Creditamount) from Credits where Creditdate between 3/1/2008 and > > 3/31/2008 > > sum (Creditamou > > --------------- > > 5,771.35 > > > > ----- Original Message ----- > > From: "Bernard Lis" <[EMAIL PROTECTED]> > > To: "RBASE-L Mailing List" <[email protected]> > > Sent: Wednesday, April 23, 2008 12:43 PM > > Subject: [RBASE-L] - RE: Group by -- need another set of eyes > > > > > >>I did an Autochk -- no errors, then did a reload, saved some space, but > >>same results????? > >> There has to be a gremlin someplace. > >> > >> Here are the results after the reload -- but this time I also did it on > >> the debit table as well as the credit table. The Debit table did not > >> duplicate, only the Credit table does? > >> > >> sel CreditDate,sum(Creditamount)=s from Credits where Creditdate between > >> 3/1/2008 and 3/31/2008 group by CreditDate > >> CreditDate sum (Creditamou > >> ---------- --------------- > >> 03/23/2008 2,952.16 > >> 03/30/2008 2,819.19 > >> 03/23/2008 2,952.16 > >> 03/30/2008 2,819.19 > >> ---------- --------------- > >> 11,542.70 > >> > >> R>sel DebitDate,sum(Debitamount)=s from Debits where Debitdate between > >> 3/1/2008 and 3/31/2008 group by DebitDate > >> DebitDate sum (Debitamoun > >> ---------- --------------- > >> 03/23/2008 825.38 > >> 03/30/2008 123.49 > >> ---------- --------------- > >> 948.87 > >> > >> ----- Original Message ----- > >> From: "Sami Aaron" <[EMAIL PROTECTED]> > >> To: "RBASE-L Mailing List" <[email protected]> > >> Sent: Wednesday, April 23, 2008 12:13 PM > >> Subject: [RBASE-L] - RE: Group by -- need another set of eyes > >> > >> > >>> Crazy stuff! Reload your database and see what you get then... > >>> > >>> Sami > >>> > >>> -----Original Message----- > >>> From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Bernard > >>> Lis > >>> Sent: Wednesday, April 23, 2008 11:04 AM > >>> To: RBASE-L Mailing List > >>> Subject: [RBASE-L] - RE: Group by -- need another set of eyes > >>> > >>> Hi Sami, > >>> You mean like this: > >>> > >>> sel CreditDate,sum(Creditamount)=s from Credits where Creditdate > >>> between > >>> 3/1/2008 and 3/31/2008 group by CreditDate > >>> CreditDate sum (Creditamou > >>> ---------- --------------- > >>> 03/23/2008 2,952.16 > >>> 03/30/2008 2,819.19 > >>> 03/23/2008 2,952.16 > >>> 03/30/2008 2,819.19 > >>> ---------- --------------- > >>> 11,542.70 > >>> > >>> ----- Original Message ----- > >>> From: "Sami Aaron" <[EMAIL PROTECTED]> > >>> To: "RBASE-L Mailing List" <[email protected]> > >>> Sent: Wednesday, April 23, 2008 11:58 AM > >>> Subject: [RBASE-L] - RE: Group by -- need another set of eyes > >>> > >>> > >>>> Bernie - > >>>> > >>>> The WHERE clause comes before the GROUP BY clause: > >>>> > >>>> sel CreditDate,sum(Creditamount)=s from Credits + > >>>> where Creditdate between 3/1/2008 and 3/31/2008 + > >>>> group by CreditDate > >>>> > >>>> Sami > >>>> ____________________________ > >>>> Sami Aaron > >>>> Software Management Specialists > >>>> 913-915-1971 > >>>> [EMAIL PROTECTED] > >>>> > >>>> > >>>> > >>>> -----Original Message----- > >>>> From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Bernard > >>>> Lis > >>>> Sent: Wednesday, April 23, 2008 10:52 AM > >>>> To: RBASE-L Mailing List > >>>> Subject: [RBASE-L] - Group by -- need another set of eyes > >>>> > >>>> Can anyone see what's wrong here? > >>>> Why is this duplicating? > >>>> > >>>> CreditDate sum (Creditamou > >>>> ---------- --------------- > >>>> 03/23/2008 2,952.16 > >>>> 03/30/2008 2,819.19 > >>>> 03/23/2008 2,952.16 > >>>> 03/30/2008 2,819.19 > >>>> ---------- --------------- > >>>> 11,542.70 > >>>> > >>>> > >>>> > >>> > >>> > >>> > >> > >> > >> > > > > > > > >
