Hi, this have nothing to do with cake, but I try to help you anyway. I think you can do something like this:
select h.name as PSC ,count(DISTINCT c.id) as Papers,avg (c.duration), sum(if(c.duration <= 155, 1, 0)), sum(IF(c.duration > 155, 1, 0)) from contracts as c inner join holders as h on h.id = c.holder_id where c.status = 1 and c.waiver =0 and c.commitee_id !=6 group by h.name hope this help. 2009/5/27 0x1A4 <[email protected]> > > select h.name as PSC ,count(DISTINCT c.id) as Papers,avg > (c.duration),count(DISTINCT c.duration > 155),count(DISTINCT 155 = > c.duration) > from > contracts as c inner > join holders as h on h.id = c.holder_id > where c.status = 1 and c.waiver =0 and c.commitee_id !=6 > group by > h.name > > I managed to get it work half way however the last 2 column is a > problem.I still finding ways on how to count records that have > duration >= 155 and duration <=155 > > On May 27, 10:09 am, 0x1A4 <[email protected]> wrote: > > hey all, > > > > i have a problem here.There are 2 tables contracts and holders. > > > > [contracts] > > -id > > -holder_id > > -duration > > -contract_no > > -contract_title > > -date_start > > -date_end > > -created > > > > [holders] > > -id > > -name > > > > i need to display it in a table order by holder's names. > > > > [holders][no of contracts][average duration][duration <= 155][duration>= > 155] > > > > [7 INC ][ 16 ][ 200 days ] > > [ 12 ][ 4 ] > > > > hope everyone could help with awesome idea how to solve it. > > > > -- Mauricio Tellez --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---
