Marc
You're correct that the group by is the reason the numbers are off, but
I believe it is behaving as expected based on what you asked for.
Example: Using my database
Select custno, sum(amount)=s fro billhist whe invdate bet 08/01/05 and
08/18/05
Yields the first five lines:
custno sum (amount)
---------- ---------------
737 16,174.81
850 57,036.00
1640 1,255.98
1765 1,120.00
The detail for that data:
sel custno, amount=s fro billhistory whe invdate bet 01/01/04 and
01/31/04 order by custno
custno amount
---------- ---------------
737 180.00
737 180.00
737 120.00
737 9,792.07
737 5,902.74
850 180.00
850 196.00
850 56,660.00
1640 1,075.98
1640 180.00
1765 1,120.00
If I group by with no sum(amount), instead of order by:
sel custno, amount=s fro billhistory whe invdate bet 01/01/04 and
01/31/04 group by custno, amount
custno amount
---------- ---------------
737 120.00
737 180.00
737 5,902.74
737 9,792.07
850 180.00
850 196.00
850 56,660.00
1640 180.00
1640 1,075.98
1765 1,120.00
See, custno 737 shows a grouped by amount of 180.00 on the second line,
but the individual select with order by clause shows the detail of two
items with a value of 180.00. Because I'm not summing the amount as
part of my group by, it's not totalling the two. This will give me
different total list amounts with the =s option.
Dawn
-----Original Message-----
From: Marc [mailto:[EMAIL PROTECTED]
Sent: Friday, August 19, 2005 10:52 AM
To: RBG7-L Mailing List
Subject: [RBG7-L] - Is this normal
Hi all
Why are the total for these 2 select statements different??
select custnum,sum(ch_price)=s from tran_hist where tr_type = 1 and
inscomp = '7' and tr_date between 6/30/05 and 7/12/05 group by custnum
select custnum,ch_price=s from tran_hist where tr_type = 1 and inscomp =
'7' and tr_date between 6/30/05 and 7/12/05 group by custnum,ch_price
I think the Group by on the second statement causes the numbers to be
off because it should be Order by?
But I would think I would get an error instead of an incorrect total or
am I mixed up?
Marc
