Count and group by

Need Help
1

Good day!
I have two tables:
First “Hospitals” - ID number Description
The second table “analyzes” - id, hospital_id, date of analysis, name of the analysis, result, patient_id.
I need to make a calculation for each hospital, and at the end, calculate the amount and display it. How can this be implemented, is there a tool?

2

I do not have a cake example, but this should give an idea, convert to cake code:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

Remember not cake code there so adjust to cake.

But I suggest you write normal sql for that using cake’s pdo instance, and learn more sql:

Not a group by, just basic example of pdo instance:

    public function getPets()
    {
        $dbh = ConnectionManager::get('default');
        $sql = "SELECT * FROM dc_pets Where `adopted` = :adpt";
        $sth = $dbh->prepare($sql);
        $params = ['adpt' => 0];
        $sth->execute($params);
        return $sth->fetchAll(\PDO::FETCH_OBJ);
    }
3

Good day!
Thanks for the help!
And how to display all this on the screen?
Unfortunately, I can only print one value, but the whole array is not.