Oracle perform a count for each row on a table-CodePudding

I have a doubt regarding a query in oracle SQL. I have a groups table, that I can query as:

SELECT * 
FROM groups g
WHERE g.owner = 123;

This would give something like:

groupId	owner
1	123
2	123
3	123
4	123

I also have another table of administrators, that I can query as:

SELECT * 
FROM admins a
ORDER BY groupId;

This would get administrators as:

adminId	userName	groupId
1	myadmin1	1
2	myAdmin2	1
3	myAdmin3	1
4	myAdmin4	2
5	myAdmin5	3
6	myAdmin6	3

That basically means that a group can have multiple administrators. I would like to count the number of administrators for each group. A result such as:

groupId	owner	adminCount
1	123	3
2	123	1
3	123	2
4	123	0

However, I cannot make a count of each administrator in the table and then make a join, as it is a table with a lot of rows. I would like to perform the count query

SELECT count(*)
FROM admins a
WHERE groupId = 1;

for each row of the groups query, such that I get the desired result without performing a count of each administrator in the table, just the ones that belong to the groups from a specific owner.

Does someone know how can I count it without counting all the rows in the administrators table?

Thanks

CodePudding user response：

The easiest and most readable variant is to use outer apply (or lateral( )):

select *
from groups g
     outer apply (
       select count(*) as adminCount
       from admins a
       where a.groupId=g.groupId
     );

Or you can get the same results using subqueries (moreover, in fact Oracle optimizer can decide to transform outer-apply/lateral to this variant, since it has "lateral view decorrelation" transformation):

select g.groupId,g.owner, nvl(a.adminCount,0) as adminCount
from groups g
     left join (
         select x.groupId, count(*) as adminCount
         from admins x
         group by x.groupId
     ) a
     on a.groupId=g.groupId;

or even group-by with join:

select g.groupId,g.owner, count(a.groupId) as adminCount
from groups g
     left join admins a
          on g.groupId=a.groupId
group by g.groupId,g.owner

https://dbfiddle.uk/a-Q_abg8

CodePudding user response：

You could use analytic function COUNT() OVER() ...

Select  Distinct
        g.GROUP_ID,
        g.OWNER,
        Count(a.ADMIN_ID) OVER(Partition By g.GROUP_ID) "COUNT_ADMINS"
From    groups g
Left Join admins a ON(a.GROUP_ID = g.GROUP_ID)
Where   g.OWNER = 123
Order By g.GROUP_ID

... this requires the Distinct keyword which could be performance costly with big datasets. I don't expect that user groups and admins are that big.
WIth your sample data:

WITH
    groups (GROUP_ID, OWNER) AS
        (
            Select 1, 123 From Dual Union ALL
            Select 2, 123 From Dual Union ALL
            Select 3, 123 From Dual Union ALL
            Select 4, 123 From Dual 
        ),
    admins (ADMIN_ID, ADMIN_USER_NAMAE, GROUP_ID) AS
        (
            Select 1, 'myadmin1', 1 From Dual Union All
            Select 2, 'myadmin2', 1 From Dual Union All
            Select 3, 'myadmin3', 1 From Dual Union All
            Select 4, 'myadmin4', 2 From Dual Union All
            Select 5, 'myadmin5', 3 From Dual Union All
            Select 6, 'myadmin6', 3 From Dual 
        )

... the result is

GROUP_ID	OWNER	COUNT_ADMINS
1	123	3
2	123	1
3	123	2
4	123	0