Combine complex query with count query

Question

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 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!

PLease provide some sample data, DDL + DML commands

learning
– learning

2022-12-07 08:05:16 +00:00
Commented Dec 7, 2022 at 8:05 — learning
– learning, Commented Dec 7, 2022 at 8:05

Albina · Accepted Answer · 2022-12-07 13:01:58Z

1

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 
           kg.id AS group_id, 
           kg.name AS name,
           kg.parent_group AS parent_group,
           kg.realm_id AS realm_id,
           ga.id AS group_attr_id,
           ga.name AS group_attr_name,
           ga.value AS group_attr_value,
           ga.value AS group_attr_group_id
FROM keycloak_group kg 
  JOIN group_attribute ga ON ga.group_id = kg.id AND ga.name = 'CompanyId'
    -- change WHERE to your condition
    WHERE ga.value = '7bc5672e-9fb9-43f2-ab2e-da03dca0c32d'
  UNION
  SELECT            
           kg.id AS group_id, 
           kg.name AS name,
           kg.parent_group AS parent_group,
           kg.realm_id AS realm_id,
           ga.id AS group_attr_id,
           ga.name AS group_attr_name,
           ga.value AS group_attr_value,
           ga.value AS group_attr_group_id 
  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 t.*, COUNT(*) over (partition by t.group_id) as members_count
FROM t
JOIN user_group_membership ugm ON ugm.group_id = t.group_id;

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

edited Dec 7, 2022 at 13:01

answered Dec 7, 2022 at 9:19

Albina

2,0213 gold badges9 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

hoozr Over a year ago

Hi, thanks for your answer. Can't see join with user_group_membership in your answer. It is table where I need to count user_id for each group.

Albina Over a year ago

@hoozr please give some test data, DDL and DML commands

hoozr Over a year ago

here it is: sqlfiddle.com/#!17/84c39/1 @albina

hoozr Over a year ago

sqlfiddle.com/#!17/84c39/25 Is it possible to have output like this? Without duplicates. Just with one new column 'membersCount' ? @albina

Albina Over a year ago

@hoozr your output still has columns with duplicated data. What do you want to get as a result? Unfortunately, I don't get your idea. You should clarify your question, expected output and grouping condition as well.

|

Collectives™ on Stack Overflow

Combine complex query with count query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related