CODEWITHSUNDEEP
sqljoincountunionwindow-functions
hoozr
hoozr

Reputation: 463

Count number of rows in complex union query

There is query:

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'
    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
 JOIN group_attribute ga ON ga.group_id = kg.id 
WHERE  ga.name = 'isCommon' and ga.value = 'true')
SELECT DISTINCT  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;

I've made with test data http://sqlfiddle.com/#!17/d938ca/30

How can I count the number of output rows here?

Upvotes: 0

Views: 40

Answers (1)

miemengniao
miemengniao

Reputation: 722

you can partition by one constant value, like below code (by constant 1), it will count all data.

SELECT DISTINCT t.*, count(*) over (partition by 1)  as totalcount,  COUNT(*) over (partition by t.group_id) as members_count

Upvotes: 1

Related Questions