Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Match two columns and add the third column in SQL

I have generated the following table from SQL database:

First_User  Second_User Count
User1       NULL        427
User3       NULL        69
User2       NULL        499
User1       User1       1
User4       User2       48
User5       User2       13

I want to create this table from the above table:

User    Count
User1   429
User2   560
User3   69
User4   48
User5   13

Match first two column values and add the third column. I am aware to do it in python but how can I do it in SQL.

In pandas, it can be done using:

df = df.groupby(['User1', 'User2'])['Count'].sum().reset_index()

Thanks in advance.

Tags:

2

Answers


  1. 0

    This can be done using UNION ALL to combine both columns into a single column, then apply GROUP BY and the aggregate function SUM() to get the expected output:

    SELECT User, SUM(Count) AS TOTAL
FROM (
  SELECT First_User AS User, Count
  FROM mytable
  WHERE First_User  IS NOT NULL
  UNION ALL
  SELECT Second_User, Count
  FROM mytable
  WHERE Second_User  IS NOT NULL
) AS s
GROUP BY User;

    Result :

    User    TOTAL
User1   429
User2   560
User3   69
User4   48
User5   13

    Demo here

    Login or Signup to reply.
  2. 0

    This actually requires a double aggregation union query.

    SELECT User, SUM(Count) AS Count
FROM
(
    SELECT First_User AS User, SUM(Count) AS Count
    FROM yourTable
    WHERE First_User IS NOT NULL
    GROUP BY First_User
    UNION ALL
    SELECT Second_User, SUM(Count)
    FROM yourTable
    WHERE Second_User IS NOT NULL
    GROUP BY Second_User
) t
GROUP BY User
ORDER BY User;

    screen capture from demo link below

    Demo

    Each half of the union aggregates by either the first or second user to get the sum of the counts (as a user might appear more than once). The outer query then aggregates by user to find the totals.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search