How do I group cells from multiple columns using the "group by" clause in a QUERY function?

Question

I have a data set with four columns of names and a single column of values. A specific name may appear multiple times per column and in any name column. Some of the cells in the name columns are also empty. It looks something like this:

Name1	Name2	Name3	Name4	Value
Andy	Dorothy	Becky		3
Becky	Edith			2
Carl				4
Dorothy	Becky	Andy	Edith	3
Carl	Andy			1

What I try to achieve is a single list where every name shows up only once with the corresponding values added up. Something like this:

Name	Sum of Values
Becky	8
Andy	7
Dorothy	6
Edith	5
Carl	5

So far, I have only been able to achieve this one name column at a time using the following function:

 =QUERY(Data!A2:E; "select Col1, SUM(Col5) where Col1 is not null group by Col1 order by SUM(Col5) DESC")

I can't figure out how to group cells from more than one column. I have tried to FLATTEN() the columns first, but I don't know how to do this only for the name columns while also keeping the corresponding value for each cell.

Answer 1

Here's one approach (non-query-based)

=sort(map(unique(tocol(A2:D;1));lambda(Σ;{Σ\sum(index(if(A2:D=Σ;E2:E;)))}));2;)