Get a particular column value count in oracle with inner join -

Get a particular column value count in oracle with inner join -

i having 2 tables joining both getting columns , values

select tbl_orderdetails.category_name, tbl_orderdetails.branch_name, tbl_ordermaster.created_date, tbl_ordermaster.user_id, tbl_orderdetails.order_details_id, tbl_orderdetails.branch_id tbl_orderdetails inner bring together tbl_ordermaster on tbl_orderdetails.order_master_id=tbl_ordermaster.ordermasterid tbl_ordermaster.user_id='12'

i want number of times particular branch name came.i used count not working , want max number of times branch name came , top 3 displayed . eg:

vellore=100, chennai=18, tvl=80, harithuwar=90

it should display only

vellore harithwar tvl

sample info orderdetails

orderdatailsid | order_master_id |branchname| category| branchid 1 | 112 | vellore | nad | 123 2 | 112 | vellore | hu | 123 3 | 113 | chennai | ji | 121 4 | 112 | vellore | hi | 123 5 | 134 | tvl | ui | 145 6 | 134 | tvl | jo | 145

masterdetails

ordermasterid | userid 112 | 12 113 | 13 134 | 14

try this

select t.*,s.* ( select td.category_name,td.branch_name,tm.created_date,tm.user_id,td.order_details_id,td.branch_id tbl_orderdetails td inner bring together tbl_ordermaster tm on td.order_master_id = tm.ordermasterid tm.user_id='12' ) t left bring together ( select t1.branch_name,count(t1.branch_name) no_of_branch tbl_orderdetails t1 inner bring together tbl_ordermaster t2 on t1.order_master_id = t2.ordermasterid t2.user_id='12' grouping t1.branch_name ) s on s.branch_name = t.branch_name

update

if want max of count

try this

select t.*,s.* ( select td.category_name,td.branch_name,tm.created_date,tm.user_id,td.order_details_id,td.branch_id tbl_orderdetails td inner bring together tbl_ordermaster tm on td.order_master_id = tm.ordermasterid tm.user_id='12' ) t inner bring together ( select m.branch_name,max(m.no_of_branch) ( select t1.branch_name,count(t1.branch_name) no_of_branch tbl_orderdetails t1 inner bring together tbl_ordermaster t2 on t1.order_master_id = t2.ordermasterid t2.user_id='12' grouping t1.branch_name ) m grouping m.branch_name ) s on s.branch_name = t.branch_name

oracle