RE: Infomix SQL help needed

If I understand your question correctly, you want to order by a computed value which you are not computing at select time. The select statement does not know what the group total of selpr is. In order to achieve this there may be a couple of options. You can select count(*) selpr, ... Or you can select count(selpr) into a temp table and join this with the original select. Without getting too deep into your ACE report I think you will have to use the second option because count(*) will lose some of the detail that you need. Rest assured it can be done in ACE, if this does not answer all your questions I could give some more exact syntax, but this should get you going. -----Original Message----- From: Sam Glattstein [SMTP:sg@norgem.com] Posted At: Thursday, December 03, 1998 12:43 PM Posted To: Informix Conversation: Infomix SQL help needed Subject: Infomix SQL help needed I am running INFORMIX SQL Version 2.0 (its old but it does the job well) In the report that is output below Customers (cusid) sell 1 product (selpr) per row which is either E, R, S, or Others (which <> E, R, or S). The report then totals each row, (group total of (selpr)) E, R, S, and Others per (cusid) along with their (group count). I am trying to change the **order by** output of the report from cusid to **group total of (selpr)** which is the far right column on the report (the sum of group totals of E, R, S, and Others). The report should look the same, but order by Totals. I have tried all combinations in "order by" but can not get the report to compile. _&k2S 12/03/1998 SOLD REPORT (selling price - count) FROM : 10/01/1998 TO : 12/01/1998 ************************************************************************ ************ CusID E - # R - # S - # Others - # Totals - # ************************************************************************ ************ BEN020 1,530 2 2,530 4 5,400 5 753 2 10,213 13 BON010 165 1 195 1 1,140 2 905 4 2,405 8 BOO010 4,810 4 860 1 6,225 7 200 1 12,095 13 BRO060 430 2 1,440 3 1,770 3 340 2 3,980 10 database northern end define variable inputd1 date variable inputd2 date end input prompt for inputd1 using "Enter Starting DATE for Range > " prompt for inputd2 using "Enter Ending DATE for Range > " end output left margin 0 top margin 2 bottom margin 1 page length 60 report to "tmph9" end select selpr, cusid, dsold, qques, clr from inventory where dsold >= $inputd1 and dsold <= $inputd2 and qques = "I" order by cusid ***** CHANGE cusid TO group total of (selpr) ****** end format page header print ascii 027, ascii 038, ascii 107, ascii 050, ascii 083, column 45, TODAY, 14 spaces, "SOLD REPORT (selling price - count)" print column 26, "FROM : ", inputd1, " TO : ", inputd2 skip 1 line print "************************************************************", "************************************************************" print 4 spaces, "CusID", 10 spaces, "E - #", 12 spaces, "R - #", 9 spaces, "S - #", 10 spaces, "Others - #", 10 spaces, "Totals - #" print "************************************************************", "************************************************************" skip 1 line after group of cusid print 4 spaces, cusid, column 17, group total of (selpr) where clr = "E" using "#,###,###", " ", group count where clr = "E" using "####", column 37, group total of (selpr) where clr = "R" using "#,###,###", " ", group count where clr = "R" using "####", column 57, group total of (selpr) where clr = "S" using "#,###,###", " ", group count where clr = "S" using "####", column 77, group total of (selpr) where clr <> "E" and clr <> "R" and clr <> "S" using "#,###,###", " ", group count where clr <> "E" and clr <> "R" and clr <> "S" using "####", column 97, group total of (selpr) using "#,###,###", " ", **** This is what I am trying to order the report by **** group count using "####" skip 1 line page trailer print 62 spaces, pageno using "Page <<<" skip 4 lines end ________________________________