Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

SQL Count Query In a Main Query

I have a Categories table. The table has an "ID", "Name" and "ID_Bond" field. It is like an endless tree. Every row is bound to another row with the ID_Bond field, it contains the ID of the main rows ID field.

I have a query like SELECT [Name] FROM Categories WHERE ID_Bond = 1. This returns all the sub rows of the row with ID=1.

My question is I need the sub categories count for each row that the query returns.

Something like SELECT [Name], (....) as _TotalSubCategoriesCount FROM Categories WHERE ID_Bond = 1.
0
emresamisuzer
Asked:
emresamisuzer
  • 5
  • 5
  • 3
  • +3
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
can you please post an example here
0
 
lofCommented:
are you interested only in the next level or do you want to have a count of all descendants of a given tree node?
0
 
awking00Commented:
Some sample data and the expected result would be very helpful.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
lofCommented:
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

Open in new window

0
 
emresamisuzerAuthor Commented:
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
0
 
igni7eCommented:
SELECT c1.Name, (select count(*) from Categories c2 where c2.ID_Bond=c1.bond_id) as TotalSubCategoriesCount FROM Categories c1
0
 
SharathData EngineerCommented:
Why the count is 2 for FPS and 1 for Strategy when both these names have the same ID_Bond as 1?
0
 
lofCommented:
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
0
 
SharathData EngineerCommented:
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
0
 
igni7eCommented:
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

0
 
igni7eCommented:
Remove the comma before the left outer join
0
 
lofCommented:
igni7e,

Sorry to say, but you are wrong.

Name                             TotalSubCategoriesCount
-------------------------------- -----------------------
Games                            1
FPS                              2
Strategy                         2
Doom                             2
HL                               2
StarCraft                        1

(6 row(s) affected)

that's the result of your query
and your query after my modifications

Name                             SubCategoryCount
-------------------------------- ----------------
FPS                              2
Strategy                         1

(2 row(s) affected)

The mistake you made is to compare C1.ID_Bond to C2.ID_Bond
rather than C2.ID_Bond to C1.ID

0
 
igni7eCommented:
Ah, I didn't realise u modified the query I wrote.
I didn't know there was an ID field.
Yes, that is correct.
0
 
igni7eCommented:
Author, incase you're confused, you should use:

SELECT
C1.Name,
c2.amount as SubCategoryCount
FROM
Categories C1
left outer join (
SELECT COUNT(*) as AMOUNT, ID_BOND
FROM Categories
group by id_bond
) C2
ON c1.id=c2.id_bond
0
 
emresamisuzerAuthor Commented:
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


0
 
SharathData EngineerCommented:
emresamisuzer - Did you try my suggestion?
0
 
lofCommented:
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
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 5
  • 5
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now