need ideas on Count()

• AnzioBake

  SSCarpal Tunnel

  Points: 4135

  More actions

  April 21, 2004 at 2:17 am

  #503602

  Simply add This:

   

  CompletedReq = Sum( Completed )

  ,UnCompleted =  count(ClientGroup.fk_clientid)  - Sum( Task.Completed )

  or

  ,UnCompleted =  Sum(

     Case Task.Completed

   

  )

• sqlpro

  Hall of Fame

  Points: 3859

  More actions

  April 21, 2004 at 2:37 pm

  #503721

  Thank u anziobake.i finally ended up doing something like ....

   

  SELECT

      cg.fk_clientid,

      cl.cl_name,

      t.fk_catid,

      c.Catname,

      [CompleteTasks] = SUM(CASE WHEN t.completed = 1 THEN 1 ELSE 0 END),

      [IncompleteTasks] = SUM(CASE WHEN t.completed = 0 THEN 1 ELSE 0 END)

      count(cg.fk_clientid) as [TotalReq],

  FROM  

      Category c

      INNER JOIN Task t ON c.Catid = t.fk_catid

      INNER JOIN ClientGroup cg ON t.Taskid = cg.fk_taskid

      INNER JOIN Client cl ON cg.fk_clientid = cl.Clientid

  GROUP BY

      cg.fk_clientid,

      cl.cl_name,

      t.fk_catid,

      c.Catname

• sqlpro

  Hall of Fame

  Points: 3859

  More actions

  April 21, 2004 at 2:44 pm

  #503722

  Hi friend

  Just to add to abv

  the abv query returns something like

  client,Category,No.ofRequests,CompletedTasks,UncompletedTasks

  Micrsosft,bugs,10,8,2

  IBM,finance,22,0,22

  IBM,fixes,1,1,0

  now how do i get total no of tasks of a client in another col

  i mean if u take abv example

  client,Category,No.ofRequests,CompletedTasks,UncompletedTasks,allrequests

  Micrsosft,bugs,10,8,2,10

  IBM,finance,22,0,22,23(here this 22+1)

  IBM,fixes,1,1,0,23

  like that bcoz i wanted to keep the clients on top who requested most .