OraFAQ Forum: SQL & PL/SQL » Join and group by on multiple tables

Home » SQL & PL/SQL » SQL & PL/SQL » Join and group by on multiple tables (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Join and group by on multiple tables [message #665061]

Mon, 21 August 2017 01:07

kishor9329
Messages: 1
Registered: August 2017

Junior Member

Test data:

ACCTS
ACCTNO	BRANCHCODE
501000524165	100
501000524220	101
501000524275	102
501000524330	103
501000524385	104
501000524440	105
501000524495	106
501000524550	107
501000524605	108
501000524660	109
501000524715	100
501000524770	101

TRANSACTION
T_id	Trans_Amt	AcctNo
1	2500	501000524165
2	5000	501000524220
3	7500	501000524275
4	10000	501000524330
5	12500	501000524385
6	15000	501000524440
7	17500	501000524495
8	20000	501000524550
9	22500	501000524605
10	25000	501000524165
11	27500	501000524220
12	30000	501000524275

BRANCHES
BRANCH_CODE	BRANCH_NAME	BRANCH_PHONE
100	BANGALORE	1234567890
101	CHENNAI  	2345678901
102	HYDERABAD	345678901
103	TRIVENDRAM	4567890123
104	KOCHI    	56789012
105	BHUBANES 	6789012345
106	MUMBAI   	7890123456
107	KOLKATA  	890124567
108	MYSORE   	901245678
109	MANDYA   	
923488890

I need output like this:

BRANCHNAME                     BRANCHPHONE                    TRANSACTIONS                    RANK                     
                JAYANAGAR                          2222                                       20000                                    1
                RAJAJINAGAR                       4444                                       40000                                    2

Report message to a moderator

Re: Join and group by on multiple tables [message #665068 is a reply to message #665061]

Mon, 21 August 2017 02:31

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: Join and group by on multiple tables [message #665075 is a reply to message #665068]

Mon, 21 August 2017 08:10

Bill B
Messages: 1971
Registered: December 2004

Senior Member

explain what you mean when you say rank? also what is the sort order of your query? is it transaction within branch?

Report message to a moderator

Previous Topic:	Solution for ora-00020 maximum number of processes
Next Topic:	Max function

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 22:56:21 CDT 2024