Re: GROUP BY Question

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, 3He 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/