How to use GROUP BY after the Having clause Mysql

Here is the query which gets the accounts for a specific user the products are related to accounts in one-to-many relation so for each product there are four permissions

All the accounts which is not Disable and also shows their permissions
I got all accounts according to scenario but the problem is if one account has more than one products then it obviously shows account id more than one time

*What i am looking for to GROUP BY the a.id after the HAVING clause which checks the permissions * but no luck getting syntax error

Error Code: 1064 right syntax to use near 'GROUP BY a.`id` LIMIT 0, 1000' at line 14

Here is my query

SELECT  a.`id` AS aid,ap.`pid`, p.`products_name`, a.accounts_account_number, c.clients_name,a.accounts_product_type AS pptype,
c.`clients_last_name`, a.`accounts_account_name`
,(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Readonly' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Readonly,
(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Buy' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Buy,
(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Sell' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Sell,
(SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Disable' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS `Disable`
 FROM `bf_clients` c INNER JOIN `bf_user_clients` uc ON (c.`id` = uc.`client_id`)
 INNER JOIN `bf_accounts` a ON (c.`id` = a.`accounts_clients`)
 INNER JOIN `bf_account_products` ap ON (a.`id` = ap.`aid`)
 INNER JOIN `bf_products` p ON (ap.`pid` = p.`id`)
 WHERE uc.`agt_user_id`=2 AND a.deleted=0 AND c.deleted=0                
 HAVING (Readonly !='' OR Buy !=''  OR Sell !=''  OR `Disable` !='' ) 
 AND `Disable` !='1'   ORDER BY a.`id`  GROUP BY a.`id`

Any help would be appreciated

You can try to use an outer select

SELECT q.*
  FROM
(
  SELECT a.`id` AS aid,ap.`pid`, p.`products_name`, a.accounts_account_number, c.clients_name,a.accounts_product_type AS pptype,
         c.`clients_last_name`, a.`accounts_account_name`,
         (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Readonly' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Readonly,
         (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Buy' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Buy,
         (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Sell' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Sell,
         (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Disable' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS `Disable`
    FROM `bf_clients` c INNER JOIN `bf_user_clients` uc ON (c.`id` = uc.`client_id`)
   INNER JOIN `bf_accounts` a ON (c.`id` = a.`accounts_clients`)
   INNER JOIN `bf_account_products` ap ON (a.`id` = ap.`aid`)
   INNER JOIN `bf_products` p ON (ap.`pid` = p.`id`)
   WHERE uc.`agt_user_id`=2 AND a.deleted=0 AND c.deleted=0                
  HAVING (Readonly !='' OR Buy !=''  OR Sell !=''  OR `Disable` !='' ) AND `Disable` !='1'   
) q
 GROUP BY aid

Move the GROUP BY to between the WHERE and the HAVING.

SELECT  
    a.`id` AS aid, 
    ap.`pid`, 
    p.`products_name`, 
    a.accounts_account_number, 
    c.clients_name, 
    a.accounts_product_type AS pptype, 
    c.`clients_last_name`, 
    a.`accounts_account_name`,
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Readonly' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Readonly,
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Buy' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Buy,
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Sell' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS Sell,
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Disable' AND uid=2 AND aid=a.id AND pid=ap.`pid`) AS `Disable`
FROM `bf_clients` c 
    INNER JOIN `bf_user_clients` uc ON (c.`id` = uc.`client_id`)
    INNER JOIN `bf_accounts` a ON (c.`id` = a.`accounts_clients`)
    INNER JOIN `bf_account_products` ap ON (a.`id` = ap.`aid`)
    INNER JOIN `bf_products` p ON (ap.`pid` = p.`id`)
WHERE uc.`agt_user_id`=2 AND a.deleted=0 AND c.deleted=0                
GROUP BY a.`id`
HAVING (Readonly !='' OR Buy !=''  OR Sell !=''  OR `Disable` !='' ) 
    AND `Disable` !='1'   
ORDER BY a.`id`  

Also, move anything that needs to be checked BEFORE the GROUP BY, into the WHERE

Since you are using the HAVING to filter on an aliased subquery, you have should either (option A) add the whole subqueries into the where clause OR (option B?) why don’t you just join the table used in the subquery?

Option A Example WHERE clause:

WHERE uc.`agt_user_id`=2 AND a.deleted=0 AND c.deleted=0 
    AND (
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Readonly' AND uid=2 AND aid=a.id AND pid=ap.`pid`) != '' OR 
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Sell' AND uid=2 AND aid=a.id AND pid=ap.`pid`) != '' OR 
        (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Disable' AND uid=2 AND aid=a.id AND pid=ap.`pid`)  != ''
        )
    and (SELECT `status` FROM `bf_account_permissions` WHERE `permission`='Disable' AND uid=2 AND aid=a.id AND pid=ap.`pid`)  != '1'
GROUP BY a.`id`
-- REMOVED THE HAVING
ORDER BY a.`id` 

Option B Revised with a LEFT JOIN:

NOTE: This option below has just been roughly put together without any tables / data, so may need some adjustment. Further suggestion to improve this are welcome!

SELECT  
    a.`id` AS aid, 
    ap.`pid`, 
    p.`products_name`, 
    a.accounts_account_number, 
    c.clients_name, 
    a.accounts_product_type AS pptype, 
    c.`clients_last_name`, 
    a.`accounts_account_name`,
    case when perms.`permission` = 'Readonly' then perms.status end as ReadOnly, 
    case when perms.`permission` = 'Buy' then perms.status end as Buy, 
    case when perms.`permission` = 'Sell' then perms.status end as Sell, 
    case when perms.`permission` = 'Disable' then perms.status end as `Disable`
FROM `bf_clients` c 
    INNER JOIN `bf_user_clients` uc ON (c.`id` = uc.`client_id`)
    INNER JOIN `bf_accounts` a ON (c.`id` = a.`accounts_clients`)
    INNER JOIN `bf_account_products` ap ON (a.`id` = ap.`aid`)
    INNER JOIN `bf_products` p ON (ap.`pid` = p.`id`)
    LEFT JOIN `bf_account_permissions` perms on (a.id = perms.id) 
        and (perms.pid = ap.`pid`)
        and (perms.uid=2)
WHERE uc.`agt_user_id`=2 AND a.deleted=0 AND c.deleted=0
    and (perms.status !='' or (perms.`permission` = 'Disable' and perms.status !='1'))
GROUP BY a.`id`
ORDER BY a.`id`