how to sum two columns from different tables MySQL

Question

I need to sum the data from two different columns located in different tables and grouped by session_id.....

I need to sum the column of spent_points + price_points grouped by session_id

this is the result i hope to get

I have tried with this query but I have only managed to group the data but I have not been able to sum it by session_id

SELECT session_details.session_id,SUM(session_details.spent_points) AS total_sum_session FROM session_details WHERE session_details.session_id IN ("-Meagevy6y9ukbmFXvB7","-Meak6dG9iqvHWfAGQvy") GROUP BY session_details.session_id UNION SELECT template_sales.session_id, SUM(template_sales.price_points) AS total_sum_sales FROM template_sales WHERE template_sales.session_id IN ("-Meagevy6y9ukbmFXvB7","-Meak6dG9iqvHWfAGQvy") GROUP BY template_sales.session_id

score 3 · Accepted Answer · answered Jul 28 '21 at 20:43

First use UNION ALL to get all the rows from the 2 tables that you want and then aggregate:

SELECT session_id, SUM(points) AS total_points
FROM (
  SELECT session_id, spent_points AS points
  FROM session_details 
  WHERE session_id IN ("-Meagevy6y9ukbmFXvB7","-Meak6dG9iqvHWfAGQvy") 
  UNION ALL
  SELECT session_id, price_points
  FROM template_sales 
  WHERE session_id IN ("-Meagevy6y9ukbmFXvB7","-Meak6dG9iqvHWfAGQvy") 
) t
GROUP BY session_id

score 1 · Answer 2 · answered Jul 28 '21 at 20:51

You're almost there:

WITH union_rows (session_id, points) AS (
        SELECT session_details.session_id, session_details.spent_points
          FROM session_details
         WHERE session_details.session_id IN ('-Meagevy6y9ukbmFXvB7','-Meak6dG9iqvHWfAGQvy')
         UNION ALL
        SELECT template_sales.session_id, template_sales.price_points
          FROM template_sales
         WHERE template_sales.session_id IN ('-Meagevy6y9ukbmFXvB7','-Meak6dG9iqvHWfAGQvy')
     )
SELECT session_id
     , SUM(points) AS total_sum
  FROM union_rows
 GROUP BY session_id
;

If you have an older version of MySQL, without WITH clause support, use a derived table instead.

The session_id filter can be done outside the CTE term, but then you may carry all those other rows as well, until the filter is processed.

score 0 · Answer 3 · answered Jul 28 '21 at 20:38

Maybe you can do a JOIN between this two tables using the session_id. After that you can sum the two attributes.

It'll be something like this:

SELECT session_details.session_id,SUM(SD.SPENT_POINTS + TSS.PRICE_POINTS ) AS total_sum_session 
FROM session_details SD
JOIN template_sales.session_id TSS ON SD.SESSION_ID = TSS.SESSION_ID
WHERE SD.session_id IN ("-Meagevy6y9ukbmFXvB7","-Meak6dG9iqvHWfAGQvy") 
GROUP BY SD.session_id

HiSura · Answer 4 · 2021-07-29T18:07:35.233

0

Try this:

SELECT A.session_id,SUM(A.Total) AS Total
FROM 
(
  SELECT session_details.session_id,session_details.spent_points AS Total
  FROM session_details 
  WHERE session_details.session_id IN ("-Meagevy6y9ukbmFXvB7","- 
  Meak6dG9iqvHWfAGQvy") 

  UNION ALL

  SELECT template_sales.session_id, template_sales.price_points AS Total
  FROM template_sales 
  WHERE template_sales.session_id 
  IN ("-Meagevy6y9ukbmFXvB7","-Meak6dG9iqvHWfAGQvy") 
) AS A
GROUP BY A.session_id

edited Jul 29 '21 at 18:07

answered Jul 28 '21 at 20:43

HiSura

132
8

1

You would need UNION ALL to avoid dropping rows if sessionId and points were the same in both tables. – Chris D Jul 28 '21 at 21:13

how to sum two columns from different tables MySQL

4 Answers4