> -----Original Message----- > From: IBM Mainframe Discussion List > [mailto:[EMAIL PROTECTED] On Behalf Of Lindy Mayfield > Sent: Monday, June 16, 2008 4:06 PM > To: IBM-MAIN@BAMA.UA.EDU > Subject: DB2 group by with date functions > > I seem to have forgotten how to do this, and I cannot seem to find the > answer in the docs or via Google. > > I want to group by using two columns which are year() and month(), but > the result set isn't grouping. > > For example: > > Select Year(Expdate), Month(Expdate) , > Category, Sum(Expamt) > from EXP.EXPENSES > group by expdate, category > order by 1 desc, 2 desc, 3 > > Gives: > > 2008 6 Adjustments 12.68 > 2008 6 Car 10.50 > 2008 6 Car 18.50 > > What is the syntax for the group columns when they are functions? > > Thanks! > Lindy
Select Year(Expdate), Month(Expdate) , Category, Sum(Expamt) from EXP.EXPENSES group by Year(expdate), Month(Expdate), category order by 1 desc, 2 desc, 3 ref: http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNAPK10/1.1. 6 -- John McKown Senior Systems Programmer HealthMarkets Keeping the Promise of Affordable Coverage Administrative Services Group Information Technology The information contained in this e-mail message may be privileged and/or confidential. It is for intended addressee(s) only. If you are not the intended recipient, you are hereby notified that any disclosure, reproduction, distribution or other use of this communication is strictly prohibited and could, in certain circumstances, be a criminal offense. If you have received this e-mail in error, please notify the sender by reply and delete this message without copying or disclosing it. ---------------------------------------------------------------------- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html