How to sum from multiple tables with different structure?-CodePudding

Hi I am attempting to create a "Reputation" for a user's profile by using SQL query to sum up the 'likes' & 'amount' of awards and subtracting the amount of 'dislikes'

I can get the sum from one table but cannot get it to work correctly with multiple tables,

The table structures are different for both tables, I cannot find any questions that are the same as mine..

Here is my 1st table "articles"

username	likes	dislikes
Bob	3	-1

Here is my 2nd table "awards"

username	amount
Bob	3

In this case it should output 5 but I can't get it to work

 SELECT SUM(rep) AS totalRep FROM 
  (
    SELECT `likes` FROM `articles` UNION ALL
    SELECT `dislikes` FROM `articles` UNION ALL
    SELECT `amount` FROM `awards` UNION ALL
  ) GROUP BY `username`

I have also tried like this..

SELECT SUM(rep) AS totalRep FROM 
  (
    SELECT SUM(likes) FROM `articles` UNION ALL
    SELECT SUM(dislikes) FROM `articles` UNION ALL
    SELECT SUM(amount) FROM `awards` UNION ALL
  ) GROUP BY `username`

and like this..

SELECT rep SUM(totrep) AS totrep FROM 
(
  SELECT likes,dislikes FROM `articles`
  UNION ALL
  SELECT amount FROM `awards`
) GROUP BY `username`

and..

SELECT SUM(likes,dislikes,amount) AS totrep
  FROM (
        SELECT likes,dislikes FROM `articles`
        UNION ALL
        SELECT amount FROM `awards`
       )  GROUP BY `username`

CodePudding user response：

For the articles table you need to sum likes dislikes and then use union all.

Try:

select username,sum(amount) as totalRep
FROM ( 
       select username,
              (sum(likes) sum(dislikes)) as amount
       from articles
       group by username
    union all
       select username,
              amount
     from awards
     ) as t1
group by  username;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=bee2cf836d5a7b76cea65e3b773e880c