Hello

I am wanting to aggregate several columns in one query My SQL is like this $sql = "SELECT P.Name, C.Position, C.PrizeMoney FROM people AS P LEFT JOIN competitions as C ON P.PeopleId = C.PeopleId

This produces data like this

Name.    Position.    PrizeMoney
John       First           30
John       First           30
John       Second      20
Phil         Second      20 
Pete        Third          10

I want to use the group facility to output data like this - (apologies for formatting!)

Name.    Position.      Positions.     TotalPrizeMoney
John       First                     2                   60
John       Second                1                   20
Phil         Second                1                   20
Pete        Third                    1                   10

So , I want to count the Positions, and sum the prize money by grouping on the first two columns.

My code below counts the positions, but doesn't total the prize money - I just get a 0 in the first TotalPrizeMoney column

$sql = "select P.Name, C.Position, C.PrizeMoney FROM people AS P LEFT JOIN competitions as C ON P.PeopleId = C.PeopleId";
       
        $this->src('people')
        ->query($sql)
        ->pipe(new Group(array(
            "by" => ["Name","Position"]
            ,"count" => "Positions"
            ,"sum" => "TotalPrizeMoney"
        )))
        ->pipe($this->dataStore('results'));

Thank you in advance.