how to sum values from subquerys

Question

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?

Answer 1

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

Answer 2

MySQL does not support Summing on an Alias Column. In order to calculate the Sum you have two possible choices. The first is to use Common Table Expression and the second is the include your sum statements with

Common Table Expression Example

WITH CTE_SUMExample() AS (ID,user_name,a170_score, atw_score, x100_score) 
(
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
)
SELECT 
 SUM(a170_score + atw_score + x100_score) as darts_total_thrown
FROM
 CTE_SUMExample;

Summing Example

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(
        (
         SELECT SUM(rounds) FROM wxe4_170 WHERE user = u.ID
        )   
      +
        (
         select IF (count(*) > 0, count(*)*66, 0) FROM wxe4_aroundtheworld WHERE user 
          = 
        u.ID
        ) 
      +
        (
         select IF (count(*) > 0, count(*)*100, 0) FROM wxe4_X100 WHERE user = u.ID 
        ) 
       ) as darts_total_thrown
FROM 
    darts.wxe4_users as u