CODEWITHSUNDEEP
sqlsql-server
Venkatesh Karamala
Venkatesh Karamala

Reputation: 93

Combining SQL join with count

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

Upvotes: 0

Views: 144

Answers (5)

Ace Amr
Ace Amr

Reputation: 108

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

Upvotes: 0

Meherzad
Meherzad

Reputation: 8553

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

Upvotes: 0

gbn
gbn

Reputation: 432210

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

Upvotes: 1

user359040
user359040

Reputation:

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

Upvotes: 1

Taryn
Taryn

Reputation: 247670

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

Upvotes: 3

Related Questions