How to choose maximum count using 'Having'-CodePudding

I think I almost figured it out but I still haven't reach the answer. Currently I need to 1. find the number of accounts that each employee made 2.find the maximum number that the employee made, and put down the personal info of the employee.

Overall code that I have so far (which is wrong) is the following, which doesn't have 'Having' and gives me error:

SELECT open_emp_id, MAX(counts) FROM accounts WHERE counts =
(select count(account_id) counts FROM accounts group by open_emp_id)

EDIT: Sorry for making it really confusing.

INSERT INTO  accounts (account_id, type_id, client_id, created,close_date,
  last_activity_date, a_status, branch_id,
  open_emp_id, avail_balance, pending_balance) 
  VALUES
(233355103, 'SAV',1651 ,'2015-07-30' ,NULL ,'2015-06-30', 'ACTIVE' ,1111 , 184167702 ,1500.00 ,1500.00 ),
(233355234, 'CD',1651 ,'2006-03-30' ,NULL ,'2018-06-30', 'ACTIVE' ,2221 ,184167713 ,1500.00 ,1500.00 ),
(233777234, 'SAV',1888 ,'2015-03-30' ,NULL ,'2019-06-30', 'ACTIVE' ,3331 ,233755101 ,1500.00 ,1500.00 ),
(233778884, 'CD',2343 ,'2015-03-30' ,NULL ,'2019-06-30' , 'ACTIVE' ,4441 ,239961844 ,1500.00 ,1500.00 ),...

INSERT INTO employee 
 (emp_id, first_name, last_name, start_date, manager_id, dept_id, job_id, branch_id)
VALUES 
(218803412, 'Michael', 'Smith', '2010-06-22',NULL,  234, 'AD_PRES', 1111),
(122212562, 'Susan', 'Barker', '2010-09-12', 218803412, 234, 'AD_VP', 1111),
(225748882, 'Alexander', 'Peter', '2010-07-03',122212562 , 334, 'IT_MANG', 1111),
(190538847, 'Robert', 'Tyler', '2010-02-09',218803412, 234, 'AC_MGR', 1111),..

Currently there are many accounts that are made by the same employees (ex)Michael can make 2 accounts, Susan can make 5 accounts, etc). accounts and employee table are linked by accounts.open_emp_id = employee.emp_id. Goal is to 1. count the number of accounts made by each employee 2. Find one or more employee with the highest number of accounts.

Hope I made the situation better. Thank you

CodePudding user response：

Use a CTE to get the counts by employee. Then get the maximum of that.

WITH counts AS (
    SELECT open_emp_id, COUNT(*) AS count
    FROM accounts
    GROUP BY open_emp_id
)

SELECT c.*
FROM counts AS C
WHERE c.count = (SELECT MAX(count) FROM counts)

CodePudding user response：

I know its long and not the best query out there but it works.

SELECT open_emp_id, COUNT(account_id) Accounts FROM accounts GROUP BY open_emp_id HAVING COUNT(account_id) = (SELECT MAX(counts) FROM (SELECT (SELECT COUNT(account_id) FROM accounts innerTable WHERE outerTable.open_emp_id = innerTable.open_emp_id) counts FROM accounts outerTable) GroupByWithSubQuery);