Home » SQL & PL/SQL » SQL & PL/SQL » Join and group by on multiple tables (Oracle 11g)
Join and group by on multiple tables [message #665061] Mon, 21 August 2017 01:07 Go to next message
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
Re: Join and group by on multiple tables [message #665068 is a reply to message #665061] Mon, 21 August 2017 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 65850
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.

Re: Join and group by on multiple tables [message #665075 is a reply to message #665068] Mon, 21 August 2017 08:10 Go to previous message
Bill B
Messages: 1802
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?
Previous Topic: Solution for ora-00020 maximum number of processes
Next Topic: Max function
Goto Forum:
  


Current Time: Tue Oct 23 02:54:59 CDT 2018