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