SQL Server How to display two separate sum from two different tables in single SQL statement

pbossiut 于 2023-05-21 发布在其他

关注(0)|答案(2)|浏览(124)

I have two tables; one shows all the event and the users who were invited to the event and their RSVP status. You can see the code I created at SQLFiddle

CREATE TABLE EventInvitation(
  InvitedUserId INT,
  EventId INT,
  IsAccepted BIT
)

INSERT INTO EventInvitation VALUES (1,1,1)
INSERT INTO EventInvitation VALUES (2,1,1)
INSERT INTO EventInvitation VALUES (1,2,1)
INSERT INTO EventInvitation VALUES (1,3,0)
INSERT INTO EventInvitation VALUES (2,3,1)
INSERT INTO EventInvitation VALUES (3,3,0)

CREATE TABLE EventRSVPComment(
  EventId INT,
  Comment VARCHAR(50)
)

INSERT INTO EventRSVPComment VALUES (1,'comment1_1')
INSERT INTO EventRSVPComment VALUES (1,'comment1_2')
INSERT INTO EventRSVPComment VALUES (1,'comment1_3')
INSERT INTO EventRSVPComment VALUES (2,'comment2_1')
INSERT INTO EventRSVPComment VALUES (2,'comment2_2')
INSERT INTO EventRSVPComment VALUES (2,'comment2_3')
INSERT INTO EventRSVPComment VALUES (3,'comment3_1')
INSERT INTO EventRSVPComment VALUES (3,'comment3_2')

The next table is the event comments:

I need to write a query to show each event with its total invites, total accepted and total comments like the table below:

So far I was able to display the invite & accepted using the query below, I could not get the total comment to show up correctly yet.

SELECT 
  EI.EventId,
  COUNT(*) AS Invited,
  SUM(CASE WHEN EI.IsAccepted = 1 THEN 1 ELSE 0 END) AS Accepted
FROM EventInvitation EI
GROUP BY EI.EventId

I have tried to fiddle with left join, inner join etc. without luck, I hope someone can point me in the right direction.

sql-server

来源：https://stackoverflow.com/questions/76282585/how-to-display-two-separate-sum-from-two-different-tables-in-single-sql-statemen

2条答案

按热度按时间

hvvq6cgz1#

In this case the easiest is to just use a correlated subquery

SELECT 
  EI.EventId,
  COUNT(*) AS Invited,
  SUM(CASE WHEN EI.IsAccepted = 1 THEN 1 ELSE 0 END) AS Accepted,
  (SELECT COUNT(*) FROM EventRSVPComment c WHERE c.EventId = EI.EventId) AS TotalComments
FROM EventInvitation EI
GROUP BY EI.EventId

SQL Fiddle

赞(0）回复(0）举报 2023-05-21

hgb9j2n62#

You can use inner join to join with a dataset of TotalComments per event :

SELECT EI.EventId, COUNT(*) AS Invited,
       COUNT(CASE WHEN EI.IsAccepted = 1 THEN 1 END) AS Accepted,
       MAX(totalComments) as totalComments
FROM EventInvitation EI
INNER JOIN (
  SELECT EventId, count(1) AS totalComments
  FROM EventRSVPComment
  GROUP BY EventId
) as s ON s.EventId = EI.EventId
GROUP BY EI.EventId;

Demo here

赞(0）回复(0）举报 2023-05-21

我来回答

SQL Server How to display two separate sum from two different tables in single SQL statement

2条答案

相关问题

热门标签

最新问答