Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 579
  • Last Modified:

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;
0
paeddy
Asked:
paeddy
  • 2
  • 2
2 Solutions
 
SharathData EngineerCommented:
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

0
 
paeddyAuthor Commented:
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?
0
 
SharathData EngineerCommented:
The reason is you are grouping the result set based on PSeqNmb. So you will be having unique PSeqNmbs in the result set. When you JOIN this result set with another table, it expects the same column as the JOIN condition.
If you try to join on a diferent column, it does not know which value to be picked for JOIN condition among the set of records grouped by PSeqNmb.on a different column (PSNmb).Eventhough you have one record for every group (PSeqNmb), it won't accept. you have to apply aggrgate functions on the non-group by columns.
0
 
prerakshethCommented:
The difference is that in the second query, Oracle does not know what to do with (potential) differnt values of PSeqNmb for the same value of PSNmb  (and it will throw this error even if you have one to one relation between these two fields, because it fails on the parse state and does not even go to execution state)
0
 
paeddyAuthor Commented:
Thank you very much guys for clearing this issue.
Its a great help for me to avoid confusion..
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now