I have problem in getting the min of column name TRANS the required output should be TOWN_HALL and 2

Question

SELECT 
    BRANCH_ADD,
    COUNT(TRANS_AMOUNT) AS TRANS 
FROM 
    (SELECT 
         A.BRANCH_ADD, C.TRANS_AMOUNT
     FROM 
         BRANCHES A, ACCTS B, TRANSACTION C
     WHERE 
         a.branch_code = b.branch_code
         AND b.acct_no = c.acct_no
     ORDER BY 
         BRANCH_ADD)
GROUP BY 
    BRANCH_ADD;

Output of the above query :

as text :

BRANCH_ADD    TRANS
------------  ------
TOWN_HALL     2
CHAMRAJPET    4
CITY_MARKET   4

I see multiple tables in your `FROM` clause, but no `JOIN`. Am I missing something? — Gordon Linoff, Dec 07 '19 at 16:35
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged — marc_s, Dec 07 '19 at 16:50

score 1 · Answer 1 · edited Dec 07 '19 at 16:50

You need some subquery and filter for the count = to max

SELECT BRANCH_ADD, my_count AS max_value
FROM (
  SELECT 
    A.BRANCH_ADD
    , count(C.TRANS_AMOUNT ) my_count
  FROM BRANCHES A
  INNER JOIN ACCTS B ON a.branch_code=b.branch_code
  INNER JOIN TRANSACTION C  ON b.acct_no=c.acct_no 
  GROUP BY A.BRANCH_ADD;
)  
WHERE  my_count = (
  select max(my_count) 
  from (
        SELECT 
        A.BRANCH_ADD
        , count(C.TRANS_AMOUNT ) my_count
      FROM BRANCHES A
      INNER JOIN ACCTS B ON a.branch_code=b.branch_code
      INNER JOIN TRANSACTION C  ON b.acct_no=c.acct_no 
      GROUP BY A.BRANCH_ADD;
  )
)

score 1 · Answer 2 · answered Dec 07 '19 at 16:59

Use ROW_NUMBER() to return the 1st row only:

SELECT t.BRANCH_ADD, t.TRANS FROM (
  SELECT A.BRANCH_ADD, COUNT(C.TRANS_AMOUNT) TRANS,
    ROW_NUMBER() OVER (ORDER BY COUNT(C.TRANS_AMOUNT)) rn
  FROM BRANCHES A
  INNER JOIN ACCTS B ON A.BRANCH_CODE = B.BRANCH_CODE 
  INNER JOIN TRANSACTION C ON B.ACCT_NO = C.ACCT_NO
  GROUP BY BRANCH_ADD
) t  
WHERE t.rn = 1

If you are using Oracle version 12+ you can also do it with FETCH:

SELECT A.BRANCH_ADD, COUNT(C.TRANS_AMOUNT) TRANS
FROM BRANCHES A
INNER JOIN ACCTS B ON A.BRANCH_CODE = B.BRANCH_CODE 
INNER JOIN TRANSACTION C ON B.ACCT_NO = C.ACCT_NO
GROUP BY BRANCH_ADD
ORDER BY TRANS
FETCH FIRST 1 ROWS ONLY

Barbaros Özhan · Answer 3 · 2019-12-07T17:22:50.227

You can use analytical functions

such as MIN(..) KEEP (DENSE_RANK FIRST ORDER BY ..) OVER (PARTITION BY ..):

SELECT * FROM
(
 SELECT A.BRANCH_ADD, C.TRANS_AMOUNT,
        MIN(C.TRANS_AMOUNT) 
        KEEP (DENSE_RANK FIRST ORDER BY C.TRANS_AMOUNT)
        OVER (PARTITION BY 0) AS lowest
   FROM BRANCHES A
   JOIN ACCTS B ON B.branch_code = A.branch_code
   JOIN TRANSACTION C ON C.acct_no = B.acct_no
)
  WHERE TRANS_AMOUNT = lowest

I have problem in getting the min of column name TRANS the required output should be TOWN_HALL and 2

3 Answers3