Solved

help with Group by clause Issue

Posted on 2011-03-11
5
560 Views
Last Modified: 2012-05-11
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
Comment
Question by:paeddy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 35113508
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
 

Author Comment

by:paeddy
ID: 35113537
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
 
LVL 41

Accepted Solution

by:
Sharath earned 250 total points
ID: 35113588
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
 
LVL 2

Assisted Solution

by:preraksheth
preraksheth earned 250 total points
ID: 35114708
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
 

Author Closing Comment

by:paeddy
ID: 35118759
Thank you very much guys for clearing this issue.
Its a great help for me to avoid confusion..
0

Featured Post

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question