MySQL – How to Sum Values from Subqueries

MySQLsubquerysum

i need to collect some values from multiple tables and sum this values as a column to the result table.
my query looks like this:

SELECT 
u.ID as id, 
(
    SELECT `meta_value` as user_name FROM `wxe4_usermeta` 
    WHERE `umeta_id` = u.ID AND `meta_key` = 'nickname'
) as user_name,
(
    SELECT SUM(rounds) FROM wxe4_170 WHERE user = u.ID
) as a170_score,
(
    select IF (count(*) > 0, count(*)*66, 0) FROM wxe4_aroundtheworld WHERE user = u.ID
) as atw_score,
(
    select IF (count(*) > 0, count(*)*100, 0) FROM wxe4_X100 WHERE user = u.ID 
) as x100_score,
SUM(a170_score + atw_score + x100_score) as darts_total_thrown
FROM darts.wxe4_users as u

the sum throws a "Error Code: 1054. Unknown column 'a170_score' in 'field list'" Error.

what i get without sum:

| id | user_name | a170_score | atw_score | x100_score |
--------------------------------------------------------
| 1  | someUser  | 449        | 3102      | 200        |

what i expect with sum:

| id | user_name | a170_score | atw_score | x100_score | darts_total_thrown |
----------------------------------------------------------------------------
| 1  | someUser  | 449        | 3102      | 200        | 3751              |

Why cant i access these values and how to resolve this?

Best Answer

You can't use the new columns that way. use a subquery to use the data to calculate

SELECT
wxe4_usern.*,(wxe4_usern.a170_score + wxe4_usern.atw_score + wxe4_usern.x100_score) as darts_total_thrown
FROM
(SELECT 
u.ID as id, 
(
    SELECT `meta_value` as user_name FROM `wxe4_usermeta` 
    WHERE `umeta_id` = u.ID AND `meta_key` = 'nickname'
) as user_name,
(
    SELECT SUM(rounds) FROM wxe4_170 WHERE user = u.ID
) as a170_score,
(
    select IF (count(*) > 0, count(*)*66, 0) FROM wxe4_aroundtheworld WHERE user = u.ID
) as atw_score,
(
    select IF (count(*) > 0, count(*)*100, 0) FROM wxe4_X100 WHERE user = u.ID 
) as x100_score
FROM darts.wxe4_users as u) wxe4_usern
Related Question