3

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?

Improve this question
1
  • You should probably simply join the tables instead of using subqueries in select. Also, please consider reading this advice Commented Jun 25, 2020 at 13:14

2 Answers 2

Reset to default
2

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
2
  • so you used "wxe4_usern." as "variable" and select all from the "from" querys + the sum. do i understand this right? Commented Jun 25, 2020 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 Commented Jun 25, 2020 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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.