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
To: RBASE-L Mailing List
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
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
> >>>>
> >>>>
> >>>>
> >>>
> >>>
> >>>
> >>
> >>
> >>
> >
> >
> >
>
>
