Post reply

Using Group By with MAX

  • October 29, 2008 at 2:08 pm

    #195476

    I have data stored in a table as follows:

    ProjectNOIYear

    24-21.672007

    24-1.632006

    2441.492005

    2498.242004

    Using a series of CASE statements I've transformed the data into the record set below for reporting:

    ProjectNOI2007NOI2006NOI2005NOI2004

    0024-21.67000

    00240-1.6300

    00240041.490

    002400098.24

    00240000

    I need to collapse this data so that there is a single project per row. I first tried using MAX(NOI...) in a group by statement but this returned a zero value for 2007 since that year has a negative NOI. I also tried MIN(NOI...) and that captured the negative amount for 2006 and 2007 but returns zero value for 2005 and 2004. How can I query this data to select the MAX non-zero value in each column?

    Thanks for any suggestions!

    David

  • October 29, 2008 at 2:31 pm

    #891482

    If the rows without data are always 0, try using SUM

  • October 29, 2008 at 2:44 pm

    #891490

    Hi Kimberly,

    Thank you for your response. Unfortunately the other values are not always zero. Sometimes year 2007 may have two NOI values for project 24 and I want to take the MAX value. Any ideas how to handle this situation?

    Thanks again!

    David

  • October 29, 2008 at 2:57 pm

    #891496

    There is probably a better way, but try something like this

    MAX(CASE WHEN fieldname = 0 THEN NULL ELSE fieldname END) - this will get you NULL if there were no non-zero values. You can wrap the MAX within an IsNull function call to handle that, if needed

  • October 29, 2008 at 3:15 pm

    #891504

    Hi Kimberly,

    That's a very good idea and it sounds like it will work for my purposes. Thanks for your help!

    David

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply