• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 133
  • Last Modified:

GROUP BY question

Hello, I must "build" a "top-ten" of loans in a library querying all the loans registered in an oracle database.
I've found the right query to do:

select Z30_REC_KEY, Z30_NO_LOANS from z30 left join z36h on z36h_rec_key = z30_rec_key where Z36H_SUB_LIBRARY  = 'COLUG' AND Z36H_LOAN_DATE BETWEEN 20120401 AND 20141113
GROUP BY z36h_rec_key

but the result has multiple entries of the same type. here follows an example of the first rows:

000222514                              26
000222514                              26
000222514                              26
000168117                              21
000168117                              21

(say..the book 000222514 has 26 loans, the book 000168117 21 loans...)
I'd like to obtain instead only one entry for each record, i.e.:
000222514                              26
000168117                              21

I suppose I must use a GROUP BY, but if I group by Z30_REC_KEY I obtain the following error:

select Z30_REC_KEY, Z30_NO_LOANS from z30 left join z36h on z36h_rec_key = z30_rec_key where Z36H_SUB_LIBRARY  = 'COLUG' AND Z36H_LOAN_DATE BETWEEN 20120401 AND 20141113
GROUP BY Z30_REC_KEY

ERROR at line 1:
ORA-00979: not a GROUP BY expression

can you help me?
thanks
fabiano
0
fabiano petrone
Asked:
fabiano petrone
1 Solution
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Use Select Distinct instead:

select distinct Z30_REC_KEY, Z30_NO_LOANS from z30 
left join z36h on z36h_rec_key = z30_rec_key 
where Z36H_SUB_LIBRARY  = 'COLUG' AND Z36H_LOAN_DATE BETWEEN 20120401 AND 20141113

Open in new window

0
 
fabiano petroneAuthor Commented:
it works perfectly, thanks!! :=))
fabiano
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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