Group By query based on Custom Field

Question

Hello I have a field name team and I would like to get the total amount that each team has been gatthered Im trying to echo the team name and total amount and group it by team but it doesn't work.

Here's my query:

 $result = $wpdb->get_results("
            SELECT 
                post.*, sum(meta.meta_value) as top_up_amount, meta.meta_value as team
            FROM 
                $wpdb->posts as post
            
            INNER JOIN
                $wpdb->postmeta as meta
            ON 
                post.ID = meta.post_id
            AND
                meta.meta_key = 'top_up_amount'
            OR
                meta.meta_key = 'team'
            WHERE 
                post.post_status = 'publish'
            AND 
                post.post_type = 'team_member'
            GROUP BY
                team
            ORDER BY
                top_up_amount DESC"
                            );

Can anyone please help?

Answer 1

Dont do SELECT post.* when you do grouping, you need to handle column values that belongs to a group properly like counting or combining them or whatever, Then do INNER JOIN individually for each meta key to have each own column.

Paste the SQL below in your phpMyAdmin and confirm you get the result you desired then you use on it on $wpdb.

SELECT  
count(post.ID) as post_count  /* Count Post belongs to a grouping */
,sum(tua.meta_value) as top_up_amount  /* Sum top_up_amount belongs to a grouping */
,team.meta_value as team_name /* column use for grouping */

FROM wp_posts as post

/* inner join postmeta with top_up_amount key  */
INNER JOIN wp_postmeta as tua ON  post.ID = tua.post_id AND tua.meta_key = 'top_up_amount'

/* inner join postmeta with team key  */
INNER JOIN wp_postmeta as team ON  post.ID = team.post_id AND team.meta_key = 'team'

WHERE  post.post_status = 'publish'
AND post.post_type = 'team_member'
GROUP BY team_name
ORDER BY top_up_amount DESC;