Skip to content
Advertisement

how to sum two columns from different tables MySQL

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

FIRST TABLE

second table

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

this is the result i hope to get

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 

Advertisement

Answer

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
Advertisement