Thanks Dawn I never really thought about this. I hope I am not misusing group by in any other places in my program.
Marc ----- Original Message ----- From: "Dawn Oakes" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[email protected]> Sent: Friday, August 19, 2005 10:10 AM Subject: [RBG7-L] - RE: Is this normal 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
