Solved

# SQL Count Query In a Main Query

Posted on 2009-12-30
309 Views
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
Question by:emresamisuzer

LVL 75

Expert Comment

can you please post an example here
0

LVL 10

Expert Comment

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

LVL 31

Expert Comment

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

LVL 10

Expert Comment

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
``````
0

Author Comment

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

LVL 4

Expert Comment

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

LVL 40

Expert Comment

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

LVL 10

Expert Comment

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

LVL 40

Expert Comment

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

LVL 4

Expert Comment

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

LVL 4

Expert Comment

Remove the comma before the left outer join
0

LVL 10

Accepted Solution

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

LVL 4

Expert Comment

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

LVL 4

Assisted Solution

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

Author Comment

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

LVL 40

Expert Comment

emresamisuzer - Did you try my suggestion?
0

LVL 10

Expert Comment

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.â€¦
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wiâ€¦
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, frâ€¦
This video discusses moving either the default database or any database to a new volume.