CODEWITHSUNDEEP
sql-serversql-server-2008
Graham
Graham

Reputation: 8151

How to select count of children in a hierarchical table based on a filter in SQL Server

Let's say I have a table TableA:

parent_id int
this_id int
filter int
this_date date

And some sample data:

parent_id = 1, this_id = 1, filter = 1, this_date = ...
parent_id = 1, this_id = 2, filter = 0, this_date = ...
parent_id = 1, this_id = 3, filter = 1, this_date = ...
parent_id = 4, this_id = 4, filter = 0, this_date = ...
parent_id = 4, this_id = 5, filter = 0, this_date = ...
parent_id = 4, this_id = 6, filter = 1, this_date = ...
parent_id = null, this_id = 7, filter = 0, this_date = ...
parent_id = null, this_id = 8, filter = 1, this_date = ...

The parent_id is always the same as one of the children's this_id. If there are no children then parent_id is null

The table can't change, that's what we have.

I have created a view to give me summaries:

SELECT ISNULL(parent_id,this_id) id, COUNT(*) numparts, MAX(this_date)...
FROM TableA
GROUP BY ISNULL(parent_id,this_id)

I want to add to my summary view the number of parts that have filter=1.

So my results for this example (if I do a SELECT * FROM theview) would be:

id  numparts dt    numOfFilter1
1      3     ...        2
4      3     ...        1
7      1     ...        0
8      1     ...        1

In reality my filter column will be a sub-query but I think I can figure that bit out once I have this bit sorted.

Upvotes: 1

Views: 151

Answers (1)

Ivan Golović
Ivan Golović

Reputation: 8832

Try it like this:

SELECT  ISNULL(parent_id,this_id) id, 
        COUNT(*) numparts, 
        MAX(this_date), 
        SUM(CASE WHEN filter = 1 THEN 1 ELSE 0 END) numOfFilter1
FROM    TableA
GROUP BY 
        ISNULL(parent_id,this_id)

Upvotes: 1

Related Questions