2
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.

Community
  • 1
  • 1
ashish jayara
  • 95
  • 1
  • 10

2 Answers2

1

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`
René Hoffmann
  • 2,766
  • 2
  • 20
  • 43
0

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'
krishn Patel
  • 2,579
  • 1
  • 19
  • 30
  • 1
    Don't you need to consider field `Type`? Something like `where Type='x'`... – René Hoffmann Feb 02 '17 at 07:31
  • 1
    Also I would use `join tableName as b on a.MenuID = b.MenuID` syntax rather than giving the join-condition in the where-clause. The `join ... on ...`-syntax has the advantage that you'll notice if the condition is missing, and won't execute a cross-join then. – René Hoffmann Feb 02 '17 at 07:35
  • @RenéHoffmann thanks for your remark, I was in hurry so I have made a mistake it – krishn Patel Feb 02 '17 at 08:05
  • 1
    @krishn patel group_concat Not a suportng tag in my sql m useing sql 2008 – ashish jayara Feb 02 '17 at 09:56