CODEWITHSUNDEEP
sqloracle-database
IBRA
IBRA

Reputation: 1782

SQL query to get the rows count

I have a table contain 3 columns sID, sUID, parentSUID with data like this:

 *sID*  |  *sUID*  |   *parentSUID*
----------------------------------
  sID1  |   sUID1  |     null
  sID1  |   sUID2  |     null
  sID1  |   sUID3  |    psUID2
  sID1  |   sUID4  |    psUID2
  sID2  |   sUID5  |     null
  sID2  |   sUID6  |    psUID5

I want to get all the sUID that doesn't has a parent and the count of children for each one, the result should be like this.

 *sID*  |  *sUID*  |    *count*
----------------------------------
  sID1  |   sUID1  |     0
  sID1  |   sUID2  |     2
  sID2  |   sUID5  |     1

I wrote a query but its give me the result without the count column, i don't know how to manage this.

Upvotes: 1

Views: 158

Answers (1)

Teddy
Teddy

Reputation: 1417

SELECT p.sID, p.sUID, count(c.sUID)
FROM a189 p left outer join a189 c on 'p' + p.sUID =  c.ParentSUID
where p.ParentSUID is null
group by p.sID, p.sUID

Besides using group by, sub query is another choice.

SELECT p.sID, p.sUID, (select count(1) FROM a189 c where 'p' + p.sUID = c.ParentSUID)
FROM a189 p
where p.ParentSUID is null

Result:

sID     sUID    Count
sID1    sUID1   0
sID1    sUID2   2
sID2    sUID5   1

Upvotes: 3

Related Questions