Joining values from a COUNT() operation (with conditions) as columns to other SELECT statements

Question

So I have four tables in my database, and I'm extracting information from two of them. The first is the events table, which lists the EventID, EventName etc.... and the EventHostID. And then I have my attendance table, which looks like this:

Attendance Columns

It has as its primary key the ID from the event and the ID from the user table, and from this table I can find out who is invited to, maybe going or going to an event.

I have four SQL statements to extract the information I want:

SELECT EventID, EventName, EventLocation, StartDate FROM events WHERE EventHostID = 0

SELECT COUNT(UserID) AS Invited FROM attendance WHERE EventID = "1" AND EvStatus = "invit"

SELECT COUNT(UserID) AS Maybe FROM attendance WHERE EventID = "1" AND EvStatus = "maybe"

SELECT COUNT(UserID) AS Going FROM attendance WHERE EventID = "1" AND EvStatus = "going"

The result of the first COUNT statement is in as follows:

Invited table i.e. a count of how many people are invited. The two other count statements are in this format.

What I want is to be able to combine the results of these four SQL statements but I'm not sure how to construct the syntax. I want something that looks like this:

 -----------------------------------------------------------------------------
 | EventID | EventName | EventLocation | StartDate | Invited | Maybe | Going |
 -----------------------------------------------------------------------------
 | 0       | Party     | Dave's house  | 08/08/17  | 20      |  5    |  17   |
 -----------------------------------------------------------------------------
 | 2       | Wedding   | Castle        | 02/02/19  | 25      | 20    |  10   |
 -----------------------------------------------------------------------------

You could also solve this with `CASE` statements. For an example see this post: [using-sql-count-in-a-case-statement](https://stackoverflow.com/a/17975288/8376046) — ewolden, Aug 02 '17 at 13:12
Possible duplicate of [Multiple aggregate functions in one SQL query from the same table using different conditions](https://stackoverflow.com/questions/2654750/multiple-aggregate-functions-in-one-sql-query-from-the-same-table-using-differen) — JeffUK, Aug 02 '17 at 13:18

Niamat H. · Answer 1 · 2017-08-02T14:20:56.167

1

You can use an inner join between events table and each status of attendance on EventID:

SELECT DISTINCT E.EventID, EventName, EventLocation, StartDate, Invited, Maybe, Going
FROM  events E
INNER JOIN (SELECT EventID,COUNT(UserID) AS Invited FROM attendance WHERE EvStatus = "invit" GROUP BY EventID) I 
ON I.EventID=E.EventID
INNER JOIN (SELECT EventID,COUNT(UserID) AS Maybe FROM attendance WHERE EvStatus = "maybe" GROUP BY EventID) M 
ON M.EventID=E.EventID
INNER JOIN (SELECT EventID,COUNT(UserID) AS Going FROM attendance WHERE EvStatus = "going" GROUP BY EventID) G 
ON G.EventID=E.EventID
WHERE EventHostID = 0;

NB. the use of DISTINCT is important to avoid duplicated rows

edited Aug 02 '17 at 14:20

answered Aug 02 '17 at 14:05

Niamat H.

114
7

While this code snippet may solve the question, [including an explanation](http://meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Matthijs Brouns Aug 02 '17 at 14:10
1

@MatthijsBrouns thanks for the tip, it's my first time that I answer a question. I edited my anwser, I hope it's clear now. – Niamat H. Aug 02 '17 at 14:23

score 0 · Answer 2 · answered Aug 02 '17 at 13:16

I think you just need simple sub queries e.g..

SELECT EventID, EventName, EventLocation, StartDate,

(SELECT COUNT(UserID) as invited
 FROM attendance 
 WHERE attendance.EventID = "1" AND EvStatus = "invit") as Invited

 FROM events WHERE EventHostID = 0

score 0 · Answer 3 · answered Aug 02 '17 at 13:26

0

select t1.EventID
      ,t1.EventName
      ,t1.EventLocation
      ,t1.StartDate
      ,sum(case t2.EvStatus='invit' then 1 else 0 end) as Invited
      ,sum(case t2.EvStatus='maybe' then 1 else 0 end) as Maybe
      ,sum(case t2.EvStatus='going' then 1 else 0 end) as Going
from events t1
left join attendance t2
  on t1.EventID=t2.EventID
/* insert_where_clause */
group by t1.EventId
         t1.EventName
         t1.EventLocation
         t1.StartDate

answered Aug 02 '17 at 13:26

user2877959

1,792
1
9
14

What does 'then 1 else 0 end' refer to? – Weirdali Aug 02 '17 at 13:29
@Weirdali It's a sum of either 1 or 0 depending on the value of EvStatus. Try it; it works. – user2877959 Aug 02 '17 at 13:31

score 0 · Accepted Answer · edited Aug 02 '17 at 14:01

SELECT EventID,
       EventName,
       EventLocation,
       StartDate,
       (SELECT COUNT(UserID) FROM attendance i WHERE i.EventID = e.Event_id AND EvStatus = "invit") AS Invited,
       (SELECT COUNT(UserID) FROM attendance m WHERE m.EventID = e.Event_id AND EvStatus = "maybe") AS Maybe,
       (SELECT COUNT(UserID) FROM attendance g WHERE g.EventID = e.Event_id AND EvStatus = "going") AS Going
FROM events e
WHERE EventHostID = 0

Joining values from a COUNT() operation (with conditions) as columns to other SELECT statements

4 Answers4