CODEWITHSUNDEEP
sqlselectgroup-bydb2
Veljko
Veljko

Reputation: 1808

SQL GROUP BY column from related table

I have table T1

ID     RESOLVER   RESOLVEDBY
1      A          GROUP1
2      A          GROUP1
3      A          GROUP1
4      B          GROUP1
5      B          GROUP1

I have also table T2

GROUPID   GROUP      PERSON
1         GROUP1     A      
2         GROUP1     B
3         GROUP1     C

I made this SQL query which return how many records are resolved by each person 

select resolver,count(*) from T1
where resolvedby='GROUP1'
group by resolver

and it returns correctly 

A 3
B 2

However I would also like to retrieve this statistics for all persons which belong to that group so for user C I would like to have value 0. So the final result should be

A 3
B 2
C 0

Upvotes: 0

Views: 438

Answers (4)

Amit Bera
Amit Bera

Reputation: 7315

Use below query to get expected output as per your requirement.

select person,count(resolver) from t2 left join t1 on t2.groups=t1.groups and t2.person = t1.resolver  group by person;

OUTPUT : 

mysql> select person,count(resolver) from t2 left join t1 on t2.person = t1.resolver  group by person;
+--------+-----------------+
| person | count(resolver) |
+--------+-----------------+
| A      |               4 |
| B      |               3 |
| C      |               0 |
+--------+-----------------+
3 rows in set (0.01 sec)

Upvotes: 0

dnoeth
dnoeth

Reputation: 60462

Based on your edit:

You need to outer join your existing Select to the 2nd table:

select t2.person, coalesce(cnt, 0)
from T2
left join
 ( -- aggregate before join (usually more efficient)
   select resolver,count(*) as cnt
   from T1
   where resolvedby='GROUP1'
   group by resolver
 ) as T1
on T1.resolver=T2.person
where t2."GROUP"='GROUP1' -- adding double quotes as GROUP is usually a keyword

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

Outer join the counts to the person table. Use COALESCE to show 0 when there is no entry in T2.

select t2.person, t2.group, coalesce(counted.cnt, 0)
from t2
left join
(
  select resolver, resolvedby, count(*) as cnt
  from T1
  group by resolver, resolvedby
) counted on counted.resolver = t2.person and counted.resolvedby = t2.group
where t2.group = 'GROUP1';

You could also use a subquery in the select clause instead:

select 
  t2.person, 
  t2.group, 
  (select count(*) from t1 where t1.resolver = t2.person and t1.resolvedby = t2.group)
from t2
where t2.group = 'GROUP1';

Upvotes: 1

Phylyp
Phylyp

Reputation: 1689

You need an outer join to return all records from T2. The below query uses a RIGHT OUTER JOIN to return all rows from T2 (i.e. including a row for C) and gets the matching counts for each of them: 

select T2.PERSON, count(T1.ID) from T1
right outer join T2 on T2.PERSON=T1.RESOLVER 
where T2.group ='GROUP1' -- If this was T1.group, we'd have to use COALESCE() to prevent dropping out rows in T2 that don't have a resolution in T1 
group by T2.PERSON

Above, we use a right outer join because we need all the rows from the table/expression to the right of the statement right outer join. If we had written the query's FROM clause with T2 first, it would have been a left outer join like so: ...T2 left outer join T1...

Upvotes: 0

Related Questions