Combine complex query with count query

I have a query which produces a result of common groups and groups filtered by tenant

SELECT  * 
FROM keycloak_group kg 
  JOIN group_attribute ga ON ga.group_id = kg.id AND ga.name = 'CompanyId'
    WHERE ga.value = @tenantKey 
   UNION  
SELECT  * FROM keycloak_group kg 
  LEFT JOIN group_attribute ga ON ga.group_id = kg.id AND ga.name = 'CompanyId'
    WHERE ga.id IS NULL 

I need开发者_JAVA百科 to count members in each group. Isolated query like that works good. But I'd prefer to combine it with first.

    SELECT kg.id,COUNT(ugm.user_id) FROM keycloak_group kg
   LEFT JOIN user_group_membership ugm ON ugm.group_id=kg.id
    GROUP BY kg.id

Is it possible to combine them? When I am trying to do like that there are errors:

[42803] ERROR: column "ga.id" must appear in the GROUP BY clause or be used in an aggregate function Position: 141

And when I finally add what it does ask for the output is not as expected and messed with data.

select COUNT(ugm.user_id),(select value from group_attribute ga2 where ga2.group_id = kg.id and ga2.name = 'description' ) as description , * 
from keycloak_group kg
join group_attribute ga on ga.group_id = kg.id AND ga.name = 'CompanyId'
LEFT JOIN user_group_membership ugm ON ugm.group_id=kg.id
where ga.value = @tenantKey
GROUP BY kg.id

union distinct

select COUNT(ugm.user_id),(select value from group_attribute ga2 where ga2.group_id = kg.id and ga2.name = 'description' ) as description, * 
from keycloak_group kg
 left join group_attribute ga on ga.group_id = kg.id AND ga.name = 'CompanyId'
 LEFT JOIN user_group_membership ugm ON ugm.group_id=kg.id
where ga.id is null
GROUP BY kg.id

Thanks for any help!

---

As there is no sample data or DDL, and I can't test my query, I suppose you should try window functions. You just need to choose a column to build a partition:

with t as (SELECT  * 
   FROM keycloak_group kg 
     JOIN group_attribute ga ON ga.group_id = kg.id AND ga.name = 'CompanyId'
      WHERE ga.value = @tenantKey 
     UNION  
  SELECT  * FROM keycloak_group kg 
    LEFT JOIN group_attribute ga ON ga.group_id = kg.id AND ga.name = 'CompanyId'
    WHERE ga.id IS NULL)
SELECT *, 
COUNT(*) over (partition by group_id) as members_count --choose a column for grouping
FROM t;

9.21. Aggregate Functions mention:

Each of the “hypothetical-set” aggregates listed in Table 9.61 is associated with a window function of the same name defined in Section 9.22.

9.22. Window Functions