Need to add a Group By statement to PHP/SQL generated from Codecharge – Phpmyadmin

I am trying to modify some PHP code that was created ages ago in Codecharge, to include a Group By clause in a MYSQL statement.

I have tested my MYSQL query and it works brilliantly in phpmyadmin. When I bring it in to the php page it kicks back an error because the WHERE and ORDER clauses are coded in separate locations, and then called in to the query string. Obviously, when the GROUP BY is left in the SQL it breaks because it is inserting it before the WHERE in the $this chain, so I need to figure out where I can insert the GROUP BY part of the code so it strings it all together in the proper order and the page loads as it should. Below is the 3 sections that pull together the SQL and clauses, and the webpage loads fine. I commented out by version of the SQL as it doesnt work properly without the GROUP BY.

//SetOrder Method @5-0FECF370
    function SetOrder($SorterName, $SorterDirection)
    {
        $this->Order = "book_name";
        $this->Order = CCGetOrder($this->Order, $SorterName, $SorterDirection, 
            "");
    }
//End SetOrder Method

//Prepare Method @5-C6449552
    function Prepare()
    {
        $this->wp = new clsSQLParameters($this->ErrorBlock);
        $this->wp->AddParameter("1", "urlshow_id", ccsInteger, "", "", $this->Parameters["urlshow_id"], "", false);
       $this->wp->Criterion[1] = $this->wp->Operation(opEqual, "ttb_books.show_id", $this->wp->GetDBValue("1"), $this->ToSQL($this->wp->GetDBValue("1"), ccsInteger),false);
        $this->Where = $this->wp->Criterion[1];

    }
//End Prepare Method

//Open Method @5-33D1EC54
    function Open()
    {
        $this->CCSEventResult = CCGetEvent($this->CCSEvents, "BeforeBuildSelect");
         $this->CountSQL = "SELECT COUNT(*)  " .
        "FROM (ttb_books INNER JOIN ttb_states ON " .
        "ttb_books.state_id = ttb_states.state_id) INNER JOIN ttb_statuses ON " .
        "ttb_books.status_id = ttb_statuses.status_id";
        $this->SQL = "SELECT *  " .
        "FROM (ttb_books INNER JOIN ttb_states ON " .
        "ttb_books.state_id = ttb_states.state_id) INNER JOIN ttb_statuses ON " .
        "ttb_books.status_id = ttb_statuses.status_id ";

        //$this->SQL = "SELECT ttb_books.book_id, ttb_books.book_name, ttb_books.show_id, ttb_states.state_name, COUNT(ttb_imgs.img_id) AS images " .
        //"FROM (ttb_books INNER JOIN ttb_states ON ttb_books.state_id = ttb_states.state_id) " .
        //"INNER JOIN ttb_statuses ON ttb_books.status_id = ttb_statuses.status_id " .
        //"LEFT JOIN ttb_imgs ON ttb_books.book_id = ttb_imgs.book_id ";
        $this->Group = "ttb_books.book_id, ttb_books.book_name, ttb_books.show_id, ttb_states.state_name";
        $this->CCSEventResult = CCGetEvent($this->CCSEvents, "BeforeExecuteSelect");
        $this->RecordsCount = CCGetDBValue(CCBuildSQL($this->CountSQL, $this->Where, ""), $this);
        $this->query(CCBuildSQL($this->SQL, $this->Where, $this->Order));
        $this->CCSEventResult = CCGetEvent($this->CCSEvents, "AfterExecuteSelect");
        $this->MoveToPage($this->AbsolutePage);
    }
//End Open Method

The original version of the code just lists all the book titles, and sorts by book name. My version also includes a COUNT, because I want to not only see all the book titles, but also a page count for each book in the list. Like I said, my new SQL works fine in phpmyadmin, and I’m sure it would on the page too if I could get the Grouping to work properly, too. Thank you for your help!