kdb/Q How to Union Join two tables, summing a specific value?

I have two tables:

info

First Last Score Full Name
Ann R 10 Ann R
Bob C 12 Bob C
Ann R 13 Ann R
Fil B 19 Fil B

summary

Full Name Sum_Scores
Ann R 23
Fil B 19
Bob C 12

The summary table lists all unique Full Name items and orders by the sum of the values in the Score column.

I am wondering how to do this, I have the Full Name column in the summary table, but not sure how I can populate the Sum_Scores field:

summary -> current version

Full Name Sum_Scores
Ann R
Bob C
Fil B

I appreciate all the help.

Not sure if I fully understand but:

q)t:([]score:10 12 13 19;FullName:`$("Ann R";"Bob C";"Ann R";"Fil B"))
q)`score xdesc select sum score by FullName from t
FullName| score
--------| -----
Ann R   | 23
Fil B   | 19
Bob C   | 12

Does this alone not give you what you need? Why do you already have a second table with FullNames? Unless the table you already have serves as a filter in which case you could do

q)t2:([]FullName:`$("Ann R";"Fil B"))
q)`score xdesc select sum score by FullName from t where FullName in t2`FullName
FullName| score
--------| -----
Ann R   | 23
Fil B   | 19

Just to add to Terry’s second solution, you can also achieve it using:

q)t2#select sum score by FullName from t
FullName| score
--------| -----
Ann R   | 23
Fil B   | 19

Leave a Comment