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;
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;
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much guys for clearing this issue.
Its a great help for me to avoid confusion..
Its a great help for me to avoid confusion..
Open in new window