CODEWITHSUNDEEP
mysqltelecommunication
ashish jayara
ashish jayara

Reputation: 95

Sql Query to get count from another row

MenuID  PreMenuID  Type
P2000   BS02        p
P8001   P2000       x

Hi Experts i have table structure given above which indicates menu id as main id and PremenuId Indicates the previous menu from which call is transferred.

Now type=x means there is a exception in the call

from the above table menuid=p8001 is transfer from P2000 as shown in premenuid

i want to get the count of exception generated in table but in mainId

the output should be smthng like ths

MenuID  Exception  OnId
p2000     1        p8001

Thanks.

Upvotes: 2

Views: 101

Answers (2)

krishn Patel
krishn Patel

Reputation: 2599

You need to join table with itself.

select a.MenuID, count(b.MenuID) as Exception, group_concat(b.MenuID) as OnID 
from tableName as a
join tableName as b on a.MenuID=b.MenuID
where a.type='x'

Upvotes: 0

René Hoffmann
René Hoffmann

Reputation: 2815

If I get your requirements right, a simple group by-Statement will do the job:

select PreMenuID as `MenuID`, count(Type) as `Exception`, MenuID as `OnID`
  where Type = x
  group by `MenuID`, `OnID`

Upvotes: 1

Related Questions