fabiano petrone
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
fabiano