CODEWITHSUNDEEP
mysqlsqljoin
SHAKIR SHABBIR
SHAKIR SHABBIR

Reputation: 1295

Count invitation response against events and response codes

I have this table for Response Codes: enter image description here

And this table for invitations: enter image description here

My query so far gives this: enter image description here

While I want to achieve this: enter image description here

MY QUERY:

SELECT 
      i.eventId
     ,code.responseCode
     ,COUNT(i.attendeeResponse) responseCount
FROM invitations i
LEFT JOIN response_codes code
    ON code.responseCode = i.attendeeResponse
GROUP BY i.eventId, code.responseCode, i.attendeeResponse;

SQLFiddle

Upvotes: 3

Views: 87

Answers (3)

Juan Ruiz de Castilla
Juan Ruiz de Castilla

Reputation: 984

Another lazy way could be:

SELECT B.EVENTID,A.RESPONSECODE,
IFNULL((SELECT COUNT(*) FROM INVITATIONS C WHERE C.EVENTID = B.EVENTID AND C.ATTENDEERESPONSE = A.RESPONSECODE),0) AS 'responseCount'
FROM
RESPONSE_CODES A,
INVITATIONS B
GROUP BY A.RESPONSECODE,B.EVENTID
ORDER BY EVENTID ASC,RESPONSECODE DESC

SQL Fiddle

Upvotes: 0

potashin
potashin

Reputation: 44611

You need to construct a cartesian product of all eventIds and responseCodes at first (you can achieve it with join without condition):

select c.eventId
     , c.responseCode
     , count( i.attendeeResponse ) as responseCount
from ( select distinct t1.responseCode
            , t2.eventId
       from `response_codes` t1
       join `invitations` t2 ) c
left join `invitations` i on c.responseCode = i.attendeeResponse and c.eventId = i.eventId
group by c.eventId, c.responseCode;

SQLFiddle

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

You need to cross join the responsecode table to get the all combinations of eventid and responsecode.

SQL Fiddle

SELECT distinct
      i.eventId
     ,code.responseCode
     ,case when t.responseCount is null then 0 
      else t.responsecount end rcount
FROM invitations i
cross JOIN response_codes code
left join 
(SELECT i.eventId
       ,code.responseCode
       ,COUNT(i.attendeeResponse) responseCount
 FROM invitations i
 JOIN response_codes code
 ON code.responseCode = i.attendeeResponse
 group by i.eventid, code.responsecode) t
on t.responsecode =code.responsecode and t.eventid = i.eventid
order by i.eventid, code.responsecode desc

Upvotes: 1

Related Questions