Get total Sum from SQL Query of two tables

Question

I have two tables which are as follows:

Table1 and Table2.

Table1 :

Group   Id
___________
Jam     J1
Jam     J2
Jam     J3
Meyo    M1
Meyo    M2
Meyo    M3
Meyo    M4
Meyo    M5
Sauce   S1
Sauce   S2
Sauce   S3
Sauce   S4

Table2:

Id  Shops  Companies
__________________
J1    20     5
J2    50     10
J3    30     10
M1    50     10
M2    50     10
M3    20     30
M4    80     25
M5    100    25
S1    50     10
S2    50     10
S3    100    30

I want a SQL query which would show me the result in total manner. The Output that I want is :

Group  Shops  Companies
________________________
Jam     100     25
Meyo    300     100
Sauce   200     50

I have done it using the PowerBI and I know it can be done using SQL too but I am not able to achieve it. I have prepared the table to learn the query. Also it would be amazing if I get to know how powerBI Measure can be different from SQL query, or are they same?

Thanks in Advance.

Answer 1

I don't see how these sums are cumulative. Presumably, you just want aggregation and regular sums:

select t1.grp, sum(t2.shops) shops, sum(t2.companies) companies
from table1 t1
inner join table2 t2 on t1.id = t2.id
group by t1.grp

Note that group is a language keyword, hence a poor choice for a column name. I renamed it to grp in the query.