Summing values from 3 tables

Question

Hi, I have 3 tables, each table will show records of users posting messages.

I have managed to sum the number of times each user has posted a message on each table and now I want to sum these 3 individual values together for each user.

Here is what I have managed so far:

Table USERMESSAGE:
SELECT U.SenderID, COUNT(U.SenderID) AS U_NUM FROM USERMESSAGE AS U WHERE U.SenderID != U.ReceiverID GROUP BY U.SenderID

Table COMMENT:
SELECT C.UserID, COUNT(C.UserID) AS C_NUM FROM COMMENT AS C GROUP BY C.UserID

Table FRIENDLIST:
SELECT F.UserID, COUNT(F.UserID) AS F_NUM FROM FRIENDLIST AS F WHERE F.ListName = 'News Feed' GROUP BY F.UserID

But not all user will post in all tables, so some UserID will not appear in some tables.

Answer 1

hi,

SELECT UserID , SUM( NUM ) as total
FROM (
SELECT U.SenderID AS UserID, COUNT(U.SenderID) AS NUM FROM USERMESSAGE AS U WHERE U.SenderID != U.ReceiverID GROUP BY U.SenderID

UNION ALL
SELECT C.UserID, COUNT(C.UserID) AS NUM FROM COMMENT AS C GROUP BY C.UserID

UNION ALL
SELECT F.UserID, COUNT(F.UserID) AS NUM FROM FRIENDLIST AS F WHERE F.ListName = 'News Feed' GROUP BY F.UserID
)
GROUP BY UserID

Answer 2

I would try something like this

DECLARE @Table1 TABLE(
     UserID INT
)

DECLARE @Table2 TABLE(
     UserID INT
)

DECLARE @Table3 TABLE(
     UserID INT
)

INSERT INTO @Table1 (UserID) SELECT 1
INSERT INTO @Table1 (UserID) SELECT 2
INSERT INTO @Table1 (UserID) SELECT 3

INSERT INTO @Table2 (UserID) SELECT 2
INSERT INTO @Table2 (UserID) SELECT 3

INSERT INTO @Table3 (UserID) SELECT 3

SELECT  ISNULL(CountTable1.CountUserIDTable1,0) +
     ISNULL(CountTable2.CountUserIDTable2,0) +
     ISNULL(CountTable3.CountUserIDTable3,0) CountOverTables,
     UserIDs.UserID
FROM    (
      SELECT DISTINCT
        UserID
      FROM @Table1
      UNION 
      SELECT DISTINCT
        UserID
      FROM @Table2
      UNION 
      SELECT DISTINCT
        UserID
      FROM @Table3
     ) UserIDs LEFT JOIN
     (
      SELECT UserID,
        COUNT(UserID) CountUserIDTable1
      FROM @Table1
      GROUP BY UserID
     ) CountTable1 ON UserIDs.UserID = CountTable1.UserID LEFT JOIN
     (
      SELECT UserID,
        COUNT(UserID) CountUserIDTable2
      FROM @Table2
      GROUP BY UserID
     ) CountTable2 ON UserIDs.UserID = CountTable2.UserID LEFT JOIN
     (
      SELECT UserID,
        COUNT(UserID) CountUserIDTable3
      FROM @Table3
      GROUP BY UserID
     ) CountTable3 ON UserIDs.UserID = CountTable3.UserID