need ideas on count() 1

[sqlpro]

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



cheers

 

[NathanGriffiths]

Not sure if this is what you are after, but this will further break down your count to show counts completed and incomplete tasks;

(I've added table aliases to hopefully make it a bit clearer)

SELECT 
	cg.fk_clientid, 
	cl.cl_name, 
	t.fk_catid, 
	c.Catname,
	[TaskComplete] = CASE WHEN t.completed = 1 THEN 'Yes' ELSE 'No' 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,
	CASE WHEN t.completed = 1 THEN 'Yes' ELSE 'No' END

Nathan


Want to get a good response to your question? Read this FAQ! -> faq183-874

 

[sqlpro]

Thanks Nathan
actually i want count of Yes and count of Nos
for example say the output col are

client,Category,No.ofRequests,CompletedTasks,UncompletedTasks

and data is

Micrsosft,bugs,10,8,2
IBM,finance,22,0,22
IBM,fixes,1,1,0

Hope i am clear.
Thanks

cheers

 

[NathanGriffiths]

I think this might give you what you're looking for;

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

Nathan


Want to get a good response to your question? Read this FAQ! -> faq183-874

 

[sqlpro]

Thank you very much Nathan.
Thats exactly what i am after

cheers

 

[sqlpro]

Hi Nathan
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 .

cheers