Get total Sum from SQL Query of two tables

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

​x
 
Group   Id___________Jam     J1Jam     J2Jam     J3Meyo    M1Meyo    M2Meyo    M3Meyo    M4Meyo    M5Sauce   S1Sauce   S2Sauce   S3Sauce   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

 
Id  Shops  Companies__________________J1    20     5J2    50     10J3    30     10M1    50     10M2    50     10M3    20     30M4    80     25M5    100    25S1    50     10S2    50     10S3    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

 
Group  Shops  Companies________________________Jam     100     25Meyo    300     100Sauce   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

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

 
select t1.grp, sum(t2.shops) shops, sum(t2.companies) companiesfrom table1 t1inner join table2 t2 on t1.id = t2.idgroup 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.

Advertisement

Answer