Solved

GROUP BY question

Posted on 2014-11-19
2
108 Views
Last Modified: 2014-11-19
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
Comment
Question by:fabianope65
2 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40451974
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
 

Author Closing Comment

by:fabianope65
ID: 40451995
it works perfectly, thanks!! :=))
fabiano
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

770 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