Re: GROUP BY Question

The original poster didn't mention which tool. These other posts discuss SQL solutions, but if you have ACE/4GL/NewEra, you could write a report with simple ORDER BY in the SQL (no SUM() or GROUP BY), then use ON EVERY ROW to report the raw data, and AFTER GROUP OF clauses for the aggregate information. In article <36000D4C.AC861265@bellsouth.net>, dagnew@charlottepipe.com wrote: > > > Peter Lancashire wrote: > > > 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! > > > > And here, is the way to do it (kinda kludgy -- but effective) -- not necessarily > very fast. Taken from Joe Celko's book 'SQL for Smarties'. Joe, btw, recommends > using a report writer tool for doing this as a faster and more powerful tool: > > Select rep, dist, salesman, sum(amt) from sales > group by rep, dist, salesman > union > select rep, dist, '{SALESMAN}', sum(amt) from sales > group by rep, dist > union > ... (you get the idea) > order by 1, 2, 3> He also comments that the { will sort after all other ASCII alphabetic strings and > suggests other ways that MAY be faster, depending on how the db server implements > nested views. Good book, worth reading. > > > 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/ > > -- Dennis J. Pimple Principal Consultant Informix Software Inc. -----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum