Re: GROUP BY Question

Jake Colman wrote: > > We have a Journal Entry table that needs to be reported on and summarized at > several different levels. > > Some questions: > > 1) If I do a GROUP BY (col1, col2, col3) will it summarize at each of those > levels? So if I want to see totals by Date, Payer, and Category, will > this GROUP BY do that? No. It will return just the totals from just one level. > > 2) Is there a way to preserve the detail records that went in to the > summarization? On the printed report, it will not be sufficient to simply > show the aggregated values. They will also want to see the records that > contributed to the final result. No, there isn't in pure SQL. SQL always deals with relational tables so rows containing something different (eg a total) are not allowed. Actually, that's not quite true; you could bodge something together but it would not be pretty and would run slowly. No doubt someone will prove me wrong with an elegant solution! If you have Informix-SQL (the tool that does the Perform screens and Ace reports, not the database engine) you could easily knock up an Ace report to do this. If you use other tools they will probably do this in their reporting modules, too. > > 3) Although groups are not guaranteed to be sorted, can I add a SORT clause > following the GROUP BY clause, and have the resultant groups be sorted? > Yes, you can: SELECT a, SUM(b), SUM(c) FROM t GROUP BY a ORDER BY a; In fact, you must do this to guarantee that the rows will be sorted. > Thanx! > > -- > Jake Colman > > Principia Partners LLC Phone: (201) 946-0300 > Harborside Financial Center Fax: (201) 946-0320 > 902 Plaza II Beeper: (800) 505-2795 > Jersey City, NJ 07311 E-mail: colman@ppllc.com > E-mail: jcolman@jnc.com > web: http://www.ppllc.com -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 --- If all else fails, read the instructions AND the release notes. All opinions are my own and not those of Bayer plc. My Internet plumbing does not allow me to mail and post news together. Sorry. --- Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/