MySQL - Wrong value

Question

I try calculate some numbers and i sweared alltime because it failing and then i tryed this:

SELECT SUM( 10 *1 ) 
FROM user_achievements
INNER JOIN achievements
WHERE user_achievements.user_id =8

and it says its: 420 ?!?

I try make this working:

SELECT SUM((SELECT score_base FROM achievements WHERE id = user_achievements.achievement_id)*((SELECT pixels_multiplier FROM achievements WHERE id = user_achievements.achievement_id)) * achievement_level) * achievement_level FROM user_achievements INNER JOIN achievements WHERE user_achievements.user_id=2

achievements: id, levels, pixels_base, score_base, pixels_multiplier

user_achievements: user_id, achievement_id, achievement_level

Can you show the create statements for the two tables. You can leave out any fields not used. — krowe
– krowe, Commented Feb 1, 2014 at 15:32

Gordon Linoff · Accepted Answer · 2014-02-01 15:06:05Z

2

If your query is return 420, that means that 42 rows are returned in the result set before the aggregation.

This is your query:

SELECT sum(10*1)
FROM user_achievements ua cross join
     achievements a
WHERE ua.user_id = 8;

If I had to guess, you are missing a join:

SELECT sum(10*1)
FROM user_achievements ua join
     achievements a
     on ua.achievement_id = a.id
WHERE ua.user_id = 8;

EDIT:

This is the query in the comment:

SELECT sum((SELECT score_base
            FROM achievements
            WHERE id = ua.achievement_id
            ) *
            (SELECT pixels_multiplier
             FROM achievements
             WHERE id = ua.achievement_id
            )
           )
FROM user_achievements ua join
     achievements a
     on ua.achievement_id = a.id
WHERE ua.user_id = 2;

It shouldn't even parse. Subqueries are not allowed in aggregations. Try this:

SELECT sum(a.score_base * a.pixels_multiplier)
FROM user_achievements ua join
     achievements a
     on ua.achievement_id = a.id
WHERE ua.user_id = 2;

edited Feb 1, 2014 at 15:06

answered Feb 1, 2014 at 14:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

7 Comments

user3249998 Over a year ago

#1054 - Unknown column 'a.achievement_id' in 'on clause'

krowe Over a year ago

Just change the a to ua and the ua to a in that comparison

Gordon Linoff Over a year ago

@user3249998 . . . I don't know the names of the fields in your table. I was showing an example of what the query would look like. Use it as a guide given your own database.

user3249998 Over a year ago

I tryed this and not working: SELECT sum((SELECT score_base FROM achievements WHERE id = user_achievements.achievement_id) *(SELECT pixels_multiplier FROM achievements WHERE id = user_achievements.achievement_id)) FROM user_achievements user_achievements join achievements a on user_achievements.achievement_id = a.id WHERE user_achievements.user_id = 2;

user3249998 Over a year ago

Both give this error: #1054 - Unknown column 'user_achievements.user_id' in 'where clause'

|

Collectives™ on Stack Overflow

MySQL - Wrong value

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related