CODEWITHSUNDEEP
mysqlsql
Enthys
Enthys

Reputation: 305

Only one record shows up

SELECT users.xp, users.clan, clans.name,

SUM(CASE WHEN users.clan = clans.name 
              THEN users.xp 
         END) AS total
FROM users, clans

WHERE users.clan = clans.name

Why does this SQL query print out only one record when there are multiple clans and in the clans are several users with xp. It does print out the correct sum of xp in the given clan that it shows, but I need the rest of the clans.

Upvotes: 1

Views: 49

Answers (2)

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

When you use a group function, mysql will "help" you by aggregating the result into a single row. To get one row per clan, use GROUP BY:

SELECT clans.name, SUM(users.xp) AS total
  FROM users, clans
 WHERE users.clan = clans.name
 GROUP BY clans.name

Upvotes: 1

deChristo
deChristo

Reputation: 1878

Because you are using SUM() which is an aggregation (group) function.

"If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows."

Have a look at: http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html

Upvotes: 1

Related Questions