need ideas on Count()

• sqlpro

  Hall of Fame

  Points: 3859

  More actions

  April 20, 2004 at 9:47 pm

  #162406

  Hi friends

  The following query returns list of our cliens requests (categorywise we mark each each client request under a category for example finance,accounts,bugs etc.,),

  category,total no of requests

  SELECT ClientGroup.fk_clientid, Client.cl_name, Task.fk_catid, Category.Catname,

      count(ClientGroup.fk_clientid) as [TotalReq]

      FROM  Category INNER JOIN

                  Task ON Category.Catid = Task.fk_catid INNER JOIN

                   ClientGroup ON Task.Taskid = ClientGroup.fk_taskid INNER JOIN

                   Client ON ClientGroup.fk_clientid = Client.Clientid

  Group by ClientGroup.fk_clientid, Client.cl_name, Task.fk_catid, Category.Catname

  how can i modify abv query to return additional info like count(uncompleted requests) i.e requests we've not finished

   and count(completed requests) i.e request which finished .(actually 5 th col in abv select list is sum of these 2 new cols).

   

   To know whether a request is finished or not there is field in task table i.e completed(0 or 1)

   

   Relations:

   Here the table Task pimary key table to ClientGroup and Category

   

   Thanks for ur ideas 🙂

• AnzioBake

  SSCarpal Tunnel

  Points: 4134

  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 .