[development-axapta] Query with ORDER BY and GROUP BY

[Werner Briedl]

Hi 
there!
 
Can 
i use the ORDER BY and the GROUP BY Clause in one SQL 
Statement?
 
In 
SQL92 it would look like this:
 
   SELECT     SUM(QTY) AS Qty, 
SUM(LINEAMOUNT) AS Amount, ITEMID   
FROM         
CUSTINVOICETRANS   WHERE     (INVOICEDATE = 
CONVERT(DATETIME, '2004-01-07 00:00:00', 102))   GROUP BY 
ITEMID   ORDER BY SUM(LINEAMOUNT) DESC
 
So i 
tried it in Axapta:
 
    while select sum(Qty), sum(LineAmount) from 
custInvoiceTrans group by ItemId order by sum(LineAmount) 
desc                                           
where custInvoiceTrans.InvoiceDate == str2date('20040107', 
321)    {        print 
custInvoiceTrans.itemId, ', ', custInvoiceTrans.Qty, ', ', 
custInvoiceTrans.LineAmount;    }
 
 
 
But 
the compiler raises a Syntax Error :-(
 
Has 
anybody an idea?
 
Regards,
Werner.







Yahoo! Groups Links

To visit your group on the web, go to:http://groups.yahoo.com/group/development-axapta/ 
To unsubscribe from this group, send an email to:[EMAIL PROTECTED] 
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.