1

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?

  • You should probably simply join the tables instead of using subqueries in select. Also, please consider reading this advice – mustaccio Jun 25 at 13:14
0

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
| improve this answer | |
  • so you used "wxe4_usern." as "variable" and select all from the "from" querys + the sum. do i understand this right? – Tobias Jun 25 at 13:44
  • yes you need a alias for the subselect table, which i called wxe4_usern (for new wxe4_user) and this is used to calculate every rows darts_total_thrown – nbk Jun 25 at 14:09
0

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.