SQL add a column with COUNT(*) to my query

I need to add a column with the content of this query :

SELECT COUNT(*) FROM account_subscriptiongroups WHERE account_subscriptiongroups.active = true AND account_subscriptiongroups.user_id = account_user.id

to this query :

SELECT 
      account_user.id as user_id, account_user.email, account_user.first_name, account_user.last_name, account_user.phone,
      account_subscriptiongroup.id as sub_group_id,
      account_adminaction.description,
      account_adminaction.id as admin_action_id,
      account_adminaction.created_on  as subscription_ended_on
    
    FROM 
      account_adminaction
    
    LEFT JOIN
      account_user ON account_user.id = account_adminaction.user_id
    LEFT JOIN 
      account_subscriptiongroup ON account_adminaction.sub_group_id = account_subscriptiongroup.id
    
    WHERE 
      account_adminaction.created_on >= '2021-04-07' AND account_adminaction.created_on <= '2021-04-13' AND
      ((account_adminaction.description LIKE 'Arrêt de l''abonnement%') OR (account_adminaction.description LIKE 'L''utilisateur a arrêté%'))
    
    ORDER BY
      subscription_ended_on

I tried adding a LEFT JOIN like that:

LEFT JOIN 
  account_subscriptiongroup all_sg ON account_user.id = account_subscriptiongroup.user_id

with this line in my WHERE statement :

AND all_sg.active = true

and this line in my SELECT :

COUNT(all_sg.id)

but I get an error :

ERROR:  column "account_user.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2:   account_user.id as user_id, account_user.email, account_us...
          ^

I don’t understand how I could perform this action properly

Answer

To count something, you need to specify a group where that count applies. So every column that you select (and is not used in an aggregate function, like COUNT or SUM), you need to mention in the GROUP BY clause.

Or to put it the other way around: the non-aggregate columns must apply to all rows that are contained in that particular COUNT.

So between the WHERE and ORDER BY clauses, add a GROUP BY clause:

    GROUP BY account_user.id, account_user.email, account_user.first_name, account_user.last_name, account_user.phone,
      account_subscriptiongroup.id,
      account_adminaction.description,
      account_adminaction.id,
      account_adminaction.created_on

---

If, on the other hand, you want a count from a different table, you can add a sub-select:

SELECT 
      account_user.id as user_id, account_user.email, account_user.first_name, account_user.last_name, account_user.phone,
      account_subscriptiongroup.id as sub_group_id,
      account_adminaction.description,
      account_adminaction.id as admin_action_id,
      account_adminaction.created_on  as subscription_ended_on,
      (SELECT COUNT(*) 
         FROM account_subscriptiongroups 
         WHERE account_subscriptiongroups.active = true 
           AND account_subscriptiongroups.user_id = account_user.id) AS groupcount
    FROM 
      account_adminaction

    LEFT JOIN
      account_user ON account_user.id = account_adminaction.user_id