SQL using partition across tables

Question

I have following two tables:

CREATE TABLE `T1` (
    `user`,
    `str_to_match`,
    `x` ,
    `y` 
);

and

CREATE TABLE `T2` (
    `user`,
    `ID`,
    `str_to_match`,
    `a`,
    `b` 
);

Following values are inserted into two tables:

INSERT INTO T1 VALUES ('U1','123', 23, 'YVAL');
INSERT INTO T1 VALUES ('U2','123', 21, 'YVAL1');
INSERT INTO T1 VALUES ('U2','121', 27, 'YVAL2');
INSERT INTO T1 VALUES ('U1','123', 28, 'YVAL3');
INSERT INTO T1 VALUES ('U1','456', 30, 'YVAL4');

INSERT INTO T2 VALUES ('U1', 1, '123', 'AVAL', 'BVAL');
INSERT INTO T2 VALUES ('U1', 2, '123', 'AVAL1', 'BVAL1');
INSERT INTO T2 VALUES ('U2', 3, '123', 'AVAL2', 'BVAL2');
INSERT INTO T2 VALUES ('U2', 4, '121', 'AVAL3', 'BVAL3');

I am trying for the below output

T1.user, T1.str_to_match, SUM(T1.x), COUNT(T1.x), T2.ID, T2.a, T2.b
U1, '123', 51, 2, 1, 'AVAL', 'BVAL'
U1, '123', 51, 2, 2, 'AVAL1', 'BVAL1'
U2, '123', 21, 1, 3, 'AVAL2', 'BVAL2'
U2, '121', 27, 1, 4, 'AVAL3', 'BVAL3'

I have used over partition by on table T1 for cols user and str_to_match and am able to get aggregation, but unable to join this with table T2 to get complete desired output. Aggregation has to be performed only if there is a match for str_to_match col in table T1 and T2 for the same user.

Here is my current query which works on table T1

SELECT SUM(x) OVER (PARTITION BY user, str_to_match), 
COUNT(x)  OVER (PARTITION BY user, str_to_match), 
str_to_match, 
user from T1 

Answer 1

This solution will give you the exact result that are expecting above :

select distinct abc.[User], abc.str_to_match, abc.sumval,
abc.countval,T2.ID, T2.a, T2.b
from
(select T1.[user],T1.str_to_match, SUM(T1.x) as sumval, COUNT(T1.x) as countval from T1 
group by T1.[user],T1.str_to_match) abc
inner join t2 on t2.[user] = abc.[user] and t2.str_to_match = abc.str_to_match;

Please find the attached screenshot links for query and output below :

query

Output