Link to home
Start Free TrialLog in
Avatar of fabiano petrone
fabiano petroneFlag for Italy

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Phillip Burton
Phillip Burton

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 fabiano petrone

ASKER

it works perfectly, thanks!! :=))
fabiano