Count the maximum number of clients and their manager ID using three tables

Question

I need to find name, age, bonus and the maximum number of clients for a manager. I managed to get everything except for the maximum number of clients. I need to get the maximum number of clients from the client table, and match the client id with the building id in the building table and lastly match the building id with the manager id in the manager table. With the manager id I'll be able to retrieve the manager details.

This is what I have done:

select mfname, mlname, TIMESTAMPDIFF(year, m.mbdate, CURDATE()) AS "Age", 
mbonus, MostClient = count(*) 
from apartment a, manager m, building b
where MostClient = b.buildingid and
  b.buildingid = b.managerid and
  b.managerid = m.managerid 
group by MostClient
having count(*)
order by max(MostClient)

but I'm facing an error as the MostClient in the where clause is showing unknown field list.

Error Code: 1054. Unknown column 'MostClient' in 'field list' How can I proceed with this? Any hints would be appreciated.

APARTMENT

CREATE TABLE apartment
(   buildingid      CHAR(3)     NOT NULL,
    aptno       CHAR(5)     NOT NULL,
anoofbedrooms   INT     NOT NULL,
ccid        CHAR(4),
rent INT NOT NULL,
PRIMARY KEY (buildingid, aptno),
FOREIGN KEY (buildingid) REFERENCES building(buildingid),
FOREIGN KEY (ccid) REFERENCES corpclient(ccid) );

BUILDING

CREATE TABLE building
(   buildingid  CHAR(3)     NOT NULL,
bnooffloors INT     NOT NULL,
bmanagerid      CHAR(4)     NOT NULL,
PRIMARY KEY (buildingid),
FOREIGN KEY (bmanagerid) REFERENCES manager(managerid) );

MANAGER

CREATE TABLE manager
(   managerid       CHAR(4)     NOT NULL,
mfname      VARCHAR(15)     NOT NULL,
mlname      VARCHAR(15)     NOT NULL,
mbdate      DATE        NOT NULL,
msalary     NUMERIC(9,2)    NOT NULL,
mbonus      NUMERIC(9,2),
mresbuildingid  CHAR(3),        
PRIMARY KEY (managerid) );

Answer 1

I might be wrong but taking a stab

    select a.mfname, a.mlname, TIMESTAMPDIFF(year, a.mbdate, CURDATE()) AS "Age", 
        a.mbonus,b.most_client from manager a,
        (
        select 
                 m.managerid,
                 count(*) as Most_client 
        from apartment a, manager m, building b

        Where    b.buildingid = a.buildingid and
                 m.managerid = b.bmanagerid 
        group by m.managerid
     -- having count(*) > 1 -- You can uncomment this line if you want
     -- managers with more than 1 client or whatever number you want evaluate 
        ) as b
        where a.managerid=b.managerid
        order by b.most_client desc

Answer 2

You cannot name a column in mysql with a statement like MostClient = count(*) Use the AS keyword count(*) AS MostClient. I think the next problem you are going to encounter is the other columns that are not aggregates are missing from your group by. Also, I don't see how the count(*) is going to equal the b.buildingid. Best of luck!