Combining SQL join with count

Question

I have two tables registered and attended, each with two columns: AttendentId and SessionId. I would like to query the count of AttendantId from these two tables individually for a particular session id.

Example:

registered

AttendantId      SessionId    
ID1              SN1    
ID2              SN2
ID3              SN1
ID4              SN3

Attended

AttendantId       SessionId    
ID1               SN1   
ID4               SN3

And I want to obtain the following output:

Count(Registered)   Count(Attended)  Session ID    
2                   1                SN1    
1                   0                SN2 
1                   1                SN3

What have you tried so far? Please add to your question so we can see where you are going wrong. — Ɖiamond ǤeezeƦ
– Ɖiamond ǤeezeƦ, Commented May 13, 2013 at 15:06
The headings for the first two columns in your example output are mixed up. — Anthony Grist
– Anthony Grist, Commented May 13, 2013 at 15:08
I tried using join based on Session ID but it is returning cross product of Attendant ID's — Venkatesh Karamala
– Venkatesh Karamala, Commented May 13, 2013 at 15:08
What is the relationship between the session ids in registered and those in attended? Are the attended session ids always a subset of those in registered? — RBarryYoung
– RBarryYoung, Commented May 13, 2013 at 15:18

Taryn · Accepted Answer · 2013-05-13 15:14:37Z

3

You could use a FULL OUTER JOIN:

select 
  coalesce(a.sessionid, r.sessionid) sessionid,
  count(r.AttendantId) countRegistered,
  count(a.AttendantId) countAttended
from registered r
full outer join attended a
  on r.sessionid = a.sessionid
  and r.AttendantId = a.AttendantId
group by coalesce(a.sessionid, r.sessionid);

See SQL Fiddle with Demo

answered May 13, 2013 at 15:14

Taryn

249k57 gold badges374 silver badges409 bronze badges

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

Comments

user359040 · Accepted Answer · 2013-05-13 15:15:19Z

1

Try:

select count(distinct registered), 
       count(distinct attended), 
       SessionId
from (select AttendantId registered, null attended, SessionId
      from registered
      union all
      select null registered, AttendantId attended, SessionId
      from Attended) sq
group by SessionId

answered May 13, 2013 at 15:15

user359040

Comments

gbn · Accepted Answer · 2013-05-13 15:17:28Z

1

SELECT
    ISNULL(ACount, 0),
    ISNULL(RCount, 0),
    X.SessionId
FROM
    (
    SELECT SessionId FROM Registered
    UNION -- implies DISTINCT
    SELECT SessionId FROM Attended
    ) X
    LEFT JOIN
    (SELECT COUNT(*) AS RCount, SessionId
     FROM Registered
     GROUP BY SessionId) R ON X.SessionId = R.SessionId
    LEFT JOIN
    (SELECT COUNT(*) AS ACount, SessionId
     FROM Attended 
     GROUP BY SessionId) A ON X.SessionId = A.SessionId

SQLFiddle

edited May 13, 2013 at 15:17

answered May 13, 2013 at 15:10

gbn

434k84 gold badges602 silver badges690 bronze badges

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

Try this query

select 
   a.sessionId, 
   case when aCnt is null then 0 else acnt end, 
   case when bCnt is null then 0 else bcnt end
from
   (select 
       sessionId, 
       count(*) aCNt 
   from 
       tbl1 
   group by 
       sessionid) a
full join
   (select 
       sessionId, 
       count(*) bCnt 
   from 
       tbl2 
   group by 
       sessionid) b
on 
   a.sessionId = b.sessionid

SQL FIDDLE:

| SESSIONID | COLUMN_1 | COLUMN_2 |
-----------------------------------
|       SN1 |        2 |        1 |
|       SN2 |        1 |        0 |
|       SN3 |        1 |        1 |

Hope this helps....

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 13, 2013 at 15:22

Meherzad

8,5611 gold badge33 silver badges40 bronze badges

Comments

Kermit · Accepted Answer · 2013-05-13 15:51:09Z

0

Assuming all session ID's exist in registered table

SELECT Sum(CASE 
             WHEN a.attendentid IS NOT NULL THEN 1 
             ELSE 0 
           end) AS Count(registered), 
       Sum(CASE 
             WHEN b.attendentid IS NOT NULL THEN 1 
             ELSE 0 
           end) AS Count(attended), 
       a.sessionid 
FROM   registered a 
       INNER JOIN attended b 
               ON a.sessionid = b.sessionid 
GROUP  BY a.sessionid

edited May 13, 2013 at 15:51

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

answered May 13, 2013 at 15:26

Ace Amr

1085 bronze badges

Collectives™ on Stack Overflow

Combining SQL join with count

registered

Attended

5 Answers 5

Comments

Comments

Comments

SQL FIDDLE:

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

registered

Attended

5 Answers 5

Comments

Comments

Comments

SQL FIDDLE:

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related