count group by problem :( - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

count group by problem :(

Author

Topic

raisor
Starting Member

28 Posts

Posted - 2004-05-08 : 05:58:47

Hi there, I'm trying something, but just can't figure out how to work it properly. Attempting to do a count :S

Following table layout:
the_ID (INT)
the_NAME (varchar 50)
the_SUBID (INT) <--- number here is always a number the same as an the_ID ---

so the contents of the table would be:

the_ID the_name the_SUBID
1 A 1
2 Asub 1
3 Asub2 1
4 B 4
5 C 5
6 Bsub 4
7 Asub3 1
etc. etc.
The root items always have the same the_ID and the_SUBID
What I want to accomplish is to get a result counting all sub a's, b's and c's as:
A (3)
B (1)
C (0)

How do I do this????
THANKS!!!!!!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-05-08 : 07:08:00

Look here for hierarchies....there are some discussions/code examples that will help you along

raisor
Starting Member

28 Posts

Posted - 2004-05-08 : 07:09:47

WILL DO, but do you have any specific links?

axisperfect
Starting Member

21 Posts

Posted - 2004-05-17 : 23:59:16

Here's my attempt, assuming that all children's the_subid refers to the root id, and the root id can be identified by the_id = the_subid:


CREATE table dttemp (
 the_ID int,
 the_name varchar(50),
 the_subid int )

insert into dttemp
select 1, 'A', 1 union all
select 2, 'Asub', 1 union all
select 3, 'Asub2', 1 union all
select 4, 'B', 4 union all
select 5, 'C', 5 union all
select 6, 'Bsub', 4 union all
select 7, 'Asub3', 1

select parent.the_name, count(children.the_id) from (select * from dttemp where the_id = the_subid) parent
left outer join (select * from dttemp where the_id <> the_subid) children 
on children.the_subid = parent.the_id 
group by parent.the_name

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources