SQL query to sum by group and inner join two tables-CodePudding

I have two tables like so:

Each row in both tables is uniquely identified by the columns week and city. I want to create one table with 5 columns (week, value_a, value_b, value1, value2) and 3 rows (1 row for each week, with the value columns being summed across each city). The final table should look exactly like this:

sum_a is the sum of value a for each week across all cities, sum_b is the sum of value_b across all cities and so on.

Here is my SQL query: SELECT * FROM table1 INNER JOIN table2 ON table1.week = table2.week AND table1.city = table2.city

CodePudding user response：

If you need to sum column relied by join you just need to sum your tables before to avoid repeat data Considere that if you have a week in your table 1 and not in the table 2 the data will not be shawn in your example

SELECT
    A1.week,
    A1.city,
    A1.value1,
    A1.value2,
    A2.value1,
    A2.value2
FROM (
    SELECT
        Week,
        city,
        sum(value1),
        sum(value2)
    FROM table1
    GROUP BY Week, city
) A1
INNER JOIN (
    SELECT
        Week,
        city,
        sum(valueA),
        sum(valueB)
    FROM table2
    GROUP BY Week, city
) A2
    ON a1.week = a2.week AND a1.city = a2.city

CodePudding user response：

the below query can give you output as expected:

SELECT table1.week, sum(value_a) as sum_a, sum(value_b) as sum_b, sum(value1) as sum_1, sum(value2) as sum_2
FROM table1
  INNER JOIN table2 ON table1.week = table2.week AND table1.city = table2.city
group by table1.week

Query can be validated by checking the link