Complicated Select Statement

I have a table that has 3 columns
id  parentid  tText
1     NULL     Lottery
2       1             SubText
3        1           MoreText
4       Null        Structured
5         4           SMenu
I need to have a select statement tat gives me the number of records that a parent (id where parentid=null) has

So my select based on the data above would be

id     tText       numRecords
1      Lottery         2
4      Structured    1
 

Larry Bristersr. DeveloperAsked:
Who is Participating?
 
derekkrommConnect With a Mentor Commented:
select t1.id, t1.ttext, count(*) numRecords from tbl t1 inner join tbl t2 on t1.id = t2.parentid
group by t1.id, t1.ttext
0
 
joshbulaCommented:
SELECT tText, COUNT(*) AS numRecords FROM table
WHERE parentid is NULL
GROUP BY tText
0
 
MarioAlcaideCommented:
SELECT a.ID, a.TTEXT, b.COUNT(*) FROM YOUR_TABLE_NAME a, YOUR_TABLE_NAME b WHERE PARENTID IS NULL AND a.ID = b.PARENTID group by a.ID,a.TTEXT;
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Larry Bristersr. DeveloperAuthor Commented:
Close enough...added a line and works great

select t1.id, t1.ttext, count(*) numRecords
from dbo.valueReportsCrystal t1 left join dbo.valueReportsCrystal t2 on t1.id = t2.parentid
where t1.parentID is null
group by t1.id, t1.ttext
0
 
Aaron ShiloChief Database ArchitectCommented:
select A1.id, A1.ttext, count(*) numRecords
from tbl A1 inner join tbl B2
on A1.id = A2.parentid
where A1.parentid isnull
group by A1.id, A1.ttext
0
 
Larry Bristersr. DeveloperAuthor Commented:
joshbula: and MarioAlcaide:

Sorry...I didn;t see your responses and hadn't refresed the page before I submitted my award
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.