Solved

GROUP BY question

Posted on 2014-11-19
2
100 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
Comment Utility
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
Comment Utility
it works perfectly, thanks!! :=))
fabiano
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now