SQL Count Query In a Main Query

can you please post an example here

are you interested only in the next level or do you want to have a count of all descendants of a given tree node?

Some sample data and the expected result would be very helpful.

I will post sample of the problem and possible solution at the same time

The attached code will create sample table called Hierarchy and an inline function that will be very helpful to solve the problem.

Once you create

select * from CountSubNodes(0,1)

will count top level nodes

select * from CountSubNodes(1,1)

will count second level nodes attached to parent with ID = 1

select * from Hierarchy
cross apply CountSubNodes(ID,1)
where ID_Bond = 0

will give you counts of items attached directly to each of the nodes at top level

select * from Hierarchy
cross apply CountSubNodes(ID,-1)
where ID_Bond = 0

will give you total counts of descendants of the nodes at top level

create table Hierarchy (ID int identity, name varchar(32), ID_Bond int)

insert into Hierarchy values ('A', 0)
insert into Hierarchy values ('B', 0)
insert into Hierarchy values ('C', 0)
insert into Hierarchy values ('D', 0)
insert into Hierarchy values ('AA', 1)
insert into Hierarchy values ('AB', 1)
insert into Hierarchy values ('AC', 1)
insert into Hierarchy values ('BA', 2)
insert into Hierarchy values ('DA', 4)
insert into Hierarchy values ('DB', 4)

declare @node int
set @node = 0;

go
alter function CountSubNodes(@node int, @depth int)
returns table
as
return (
	with tree as
	(
		select *
			,0 as Distance
		from Hierarchy where ID_Bond = @node
		union all
		select H.* 
			, Distance+1
		from Hierarchy H 
		inner join tree T on H.ID_Bond = T.ID 
	)
	select count(*) as SubNodeCount
	from tree
	where (@depth<0 or Distance<@depth)
)
go

Select allOpen in new window

Only one level (the next level) is enough.

ID     Name               ID_Bond
-------------------------------
1     Games              -1
2     FPS                    1
3     Strategy             1
4     Doom                 2
5     Half_life             2
6     Starcraft            3


For example I need the subcategories for ID=1. It should give as:

FPS           2
Strategy    1

SELECT c1.Name, (select count(*) from Categories c2 where c2.ID_Bond=c1.bond_id) as TotalSubCategoriesCount FROM Categories c1

Why the count is 2 for FPS and 1 for Strategy when both these names have the same ID_Bond as 1?

I think igni7e sample will return wrong values

SELECT C1.Name
   ,(SELECT count(*)  FROM Categories C2
     WHERE C2.ID_Bond = C1.ID
    ) as SubCategoryCount
FROM Categories C1
WHERE C1.ID_Bond = 1

it will return what you expect
but on big tables CTE i used in my previous example will perform better

If you want the count as 2 for FPS and 1 for Strategy then you can try like this.

SELECT c1.Name, (select count(*) from Categories c2
                  where c2.ID_Bond=c1.ID_Bond and c2.Name >= c1.Name) as TotalSubCategoriesCount
 FROM Categories c1
where ID_Bond = 1

If that is a mistake and you want the count as 2 for both the records then you can try like this.

select Name,count(ID) over (partition by ID_Bond) as TotalSubCategoriesCount
  from Categories
 where ID_Bond = 1

First of all, no, it won't return incorrect values.

2nd of all, if you're worried about efficiency, then just do the following:

SELECT
C1.Name,
c2.amount
FROM
Categories C1,
left outer join (
SELECT COUNT(*) as AMOUNT, ID_BOND
FROM Categories
group by id_bond
) C2
ON c1.id_bond=c2.id_bond

Remove the comma before the left outer join

Ah, I didn't realise u modified the query I wrote.
I didn't know there was an ID field.
Yes, that is correct.

Thanks, I have tried your solution and it seems to work but I have problem. In the example I have only a one main group named Games. But actually I will have about 20 of the main category.

ID     Name               ID_Bond
-------------------------------
1     Games              -1
2     FPS                    1
3     Strategy             1
4     Doom                 2
5     Half_life             2
6     Starcraft            3
7     Games2             -1
8     FPS2                  7
9     Doom2              8
10     Half_life2          8
.
.
.

For example, I only need the subcategory values just for the "Games" not "Games2". It sounds simple but I couldn't do it, sorry... I can pass the [ID] of the main category to my query.

Your query gave me a result like this:
Games         2
Games2       1

emresamisuzer - Did you try my suggestion?

SELECT C1.Name
   ,(SELECT count(*)  FROM Categories C2
     WHERE C2.ID_Bond = C1.ID
    ) as SubCategoryCount
FROM Categories C1
WHERE C1.ID_Bond in (SELECT distinct ID from Categories where ID_Bond=-1)
GROUP BY C1.Name

this query will give you the counts for all top level categories