Syntax diagram for PROJECT does not allow GROUP BY and the USING clause must be followed by a list of columns. Also your GROUP BY clause is not correct.
If you create the table with no rows you can achieve what you want by doing this: INSERT INTO da_peachpur (carrierinvnb,carrieramt,idnbr,invoicedt) + SELECT Invoice_nb,SUM(carrier_amt),pt_nbr,da_invoice_dt + FROM vrecon WHERE da_invoice_dt=05/23/05 + GROUP BY Invoice_nb, pt_nbr, da_invoice_dt If you leave out the WHERE clause it will give you the total for other invoices on other days as well. Regards, Stephen Markson ForenSys The Forensic Systems Group www.ForenSys.ca 416 512 6950 > -----Original Message----- > From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Claudine > Robbins > Sent: May 25, 2005 5:07 PM > To: RBG7-L Mailing List > Subject: [RBG7-L] - Group by Having > > Hi everyone, > > In vrecon, I have: > Invoice_nb, carrier_amt, da_invoice_nb, pt_nbr, da_invoice_dt > 7511481 430.37 DA-0001 14 05/23/05 > 7511481 199.38 DA-0002 14 05/23/05 > > And I want: > carrierinvnb, carrieramt, dainvnb, idnbr, invoicedt > 7511481 627.75 doesn't matter 14 05/23/05 > > DROP TABLES da_peachpur NOCHECK > PROJECT da_peachpur FROM vrecon USING + > carrierinvnb,SUM(carrieramt),dainvnb,idnbr,invoicedt + > GROUP BY invoice_nb carrier_amt HAVING da_invoice_dt = 05/23/05 > RETURN > > I get a syntax error on line 4 (the GROUP BY line) > > What am I doing wrong? > > TIA, > > Claudine :)
