count same column using different criteria

[darsha]

I need to summarise a table to get output something like this
A    15    10
B    18    11
C    20    13

where:
the first column is the value in the field being summarised (let's call it category)
the second column the count (by category) for all records
the third column is the count (by category) for a filtered subset of records

I need to diplay all categories from a lookup table, displaying zero if there are no matching records in the data table

[HansV]

One way to do this is as follows:

Step 1: create a totals query that groups by category and counts another (non-blank) field.

Step 2: create a totals query that groups by category and counts another (non-blank) field, and that filters the records; use Where as Total: option for fields that are used for the criteria.

Step 3: create a new query, add both totals queries and join them on the category field. Add the category field from the first totals query and the count field from both totals queries.

[darsha]

That's kind of what I've tried. I should have added that I'm trying to do this a single stored proceedure in SQL server.
This is what I had originally for a single summary
SELECT c.CategoryID, c.Description, ( ISNULL( COUNT(t1.TaskID), 0 ) ) AS TaskCount
FROM stbl_Category as c
LEFT OUTER JOIN (SELECT * FROM dtbl_Tasks WHERE TaskListID = @TaskListID AND Exclude <> 1) as t1 ON t1.Category = c.CategoryID
GROUP BY c.CategoryID, c.Description ORDER By c.CategoryID

but when I try to add in the second summary (see below), it takes ages and returns the wrong number of records
SELECT c.CategoryID, c.Description, ( ISNULL( COUNT(t1.TaskID), 0 ) ) AS TaskCount, ( ISNULL( COUNT(t2.TaskID), 0 ) ) AS TaskCount_All
FROM stbl_Category as c
LEFT OUTER JOIN (SELECT * FROM dtbl_Tasks WHERE TaskListID = @TaskListID AND Exclude <> 1) as t1 ON t1.Category = c.CategoryID
LEFT OUTER JOIN (SELECT * FROM dtbl_Tasks WHERE TaskListID = @TaskListID) as t2 ON c.CategoryID = t2.Category
GROUP BY c.CategoryID, c.Description ORDER By c.CategoryID

I can see it's wrong but not how to do this in a single proceedure

[HansV]

Try this:

SELECT stbl_Category.CategoryID, stbl_Category.Description, qryAll.CountAll, qrySel.CountSel FROM (stbl_Category LEFT OUTER JOIN (SELECT CategoryID, Count(*) AS CountAll FROM dtbl_Tasks WHERE TaskListID = @TaskListID GROUP BY CategoryID) As qryAll ON stbl_Category.CategoryID = qryAll.CategoryID) LEFT JOIN (SELECT CategoryID, Count(*) AS CountSel FROM dtbl_Tasks WHERE TaskListID = @TaskListID AND Exclude <> 1 GROUP BY CategoryID) AS qrySel ON stbl_Category.CategoryID = qrySel.CategoryID

[darsha]

brilliant

thank you so much