Link to home
Start Free TrialLog in
Avatar of paeddy
paeddy

asked on

help with Group by clause Issue

HI,
     I am trying to group by the PSNmb as in the query 1. It works fine.
PSNmb and PSeqNmb are in the same table b. PSeqNmb is the Primary key on table b.
 Each PSNmb has a unique PSeqNmb assigned to it. (so technically, PSNmb and PSeqNmb which ever is if referred, tells about the same info.) The query is actually big, which produces the same results and the same issue, I just truncated the query to be easy.
If I use the query 2, I get that 'ORA-00979: not a GROUP BY expression'.
I am trying to understand the reason behind this. Ideally, two grouping would provide the same grouping results I thought.
Can anyone explain the difference between these two scenarios? This would help me for other queries where I use group by clause.

query 1:
select  b.PSNmb ,
        (select sum(x.Amount *
       (((CASE WHEN x.FCd = 'A' then 1 else 0 end)
       - (CASE WHEN x.FCd = 'B' then 1 else 0 end)) ))
        from TranTbl x
        where x.AceNmb = b.PSNmb )
    from PSTbl b
    where b.PtId =  @PtId
    group by b.PSNmb ;

query 2:
select  b.PSeqNmb,
        (select sum(x.Amount*
       (((CASE WHEN x.FCd = 'A' then 1 else 0 end)
       - (CASE WHEN x.FCd = 'B' then 1 else 0 end)) ))
        from TranTbl x
        where x.AceNmb = b.PSNmb )
    from PSTbl b
    where b.PtId =  @PtId
    group by b.PSeqNmb;
Avatar of Sharath S
Sharath S
Flag of United States of America image

You have join condition on PSNmb in the sub-query. Change it to PSeqNmb and try.
select  b.PSeqNmb,
        (select sum(x.Amount*
       (((CASE WHEN x.FCd = 'A' then 1 else 0 end)
       - (CASE WHEN x.FCd = 'B' then 1 else 0 end)) ))
        from TranTbl x 
        where x.AceNmb = b.PSeqNmb ) 
    from PSTbl b
    where b.PtId =  @PtId 
    group by b.PSeqNmb;

Open in new window

Avatar of paeddy
paeddy

ASKER

Thank you very much.  Yes it works if I use the PSeqNmb.
It also works if I modify the query like below:
select  b.PSNmb ,
      SUM(  (select sum(x.Amount *
       (((CASE WHEN x.FCd = 'A' then 1 else 0 end)
       - (CASE WHEN x.FCd = 'B' then 1 else 0 end)) ))
        from TranTbl x
        where x.AceNmb = b.PSNmb ) )
    from PSTbl b
    where b.PtId =  @PtId
    group by b.PSNmb ;

I was trying to understand the difference. can you explain to me please?
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of paeddy

ASKER

Thank you very much guys for clearing this issue.
Its a great help for me to avoid confusion..