MYSQL SUM TWO COLUMNS

Question

I have this situation:

SELECT uf.*, SUM(uto.points) AS total_points_live, 
             SUM(utl.points) AS total_points_online,
             (total_points_live + total_points_online) AS total_points
FROM users AS uf    
            LEFT JOIN users_tourney_online AS uto
                ON uto.id_users = uf.id 
            LEFT JOIN users_tourney_live AS utl
                ON utl.id_users = uf.id
GROUP BY uf.id ORDER BY total_points DESC

But not working because aliases cannot be used.

Can you help me?

Regards,

1 column group by may produce indeterminate columns when you select * and is illegal in most sql dialects when there are more non aggregate columns in the select clause than the group by. And there is a good chance you may trip up on this if you ever upgrade or have an environment where 'only_full_group_by' is set. — P.Salmon
– P.Salmon, Commented Apr 3, 2020 at 13:56

GMB · Accepted Answer · 2020-04-03 14:13:32Z

3

You cannot use an alias defined in the same SELECT clause it is defined.

You would need to repeat the expression:

SUM(uto.points) + SUM(utl.points) AS total_points

If the SUMs may be NULL:

COALESCE(SUM(uto.points), 0) + COALESCE(SUM(utl.points), 0) AS total_points

Alternatively, you can use a subquery:

SELECT t.*, coalesce(total_points_live, 0) + coalesce(total_points_online, 0) total_points
FROM (
    SELECT uf.*, SUM(uto.points) AS total_points_live, SUM(utl.points) AS total_points_online
    FROM ...
    GROUP BY uf.id
) t
ORDER BY total_points desc

Unrelated note: GROUP BY and SELECT * do not go along well together; it is a good practice to enumerate all non-aggregated columns in the GROUP BY clause (although MySQL is somewhat lax about it).

edited Apr 3, 2020 at 14:13

answered Apr 3, 2020 at 13:52

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL SUM TWO COLUMNS

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related