MYSQL SUM and INNER JOIN

Question

I want to have the name in the first colomn but i get an error. When i Let P.name out, the query works but I can't see the player name.

Right now I have the following query:

SELECT P.Name, P.Playernr, SUM (F.Amount)
FROM FINES F
INNER JOIN PLAYERS P
ON F.Playernr = P.Playernr
GROUP BY  P.Playernr

Thanks in advance for help

Onno

add the non-aggregated column in the GROUP BY clause. example, GROUP BY P.Name, P.Playernr — John Woo
– John Woo, Commented Nov 12, 2013 at 12:47
Your configuration must have the ONLY_FULL_GROUP_BY option enabled. — Barmar
– Barmar, Commented Nov 12, 2013 at 12:50
Just add the P.Name in the Group by, so the aggregate function can work — Nadeem_MK
– Nadeem_MK, Commented Nov 12, 2013 at 12:55

Muhammad Hani · Accepted Answer · 2013-11-12 12:53:29Z

3

You should add it to the GROUP BY clause.

SELECT P.Name, P.Playernr, SUM (F.Amount)
FROM FINES F
INNER JOIN PLAYERS P
ON F.Playernr = P.Playernr
GROUP BY  P.Name,P.Playernr

answered Nov 12, 2013 at 12:53

Muhammad Hani

8,6725 gold badges32 silver badges44 bronze badges

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

Comments

valex · Accepted Answer · 2013-11-12 12:52:37Z

0

Try this:

SELECT MAX(P.Name) as Name, P.Playernr, SUM (F.Amount)
FROM FINES F
INNER JOIN PLAYERS P
ON F.Playernr = P.Playernr
GROUP BY  P.Playernr

or add P.Name to the Group BY

SELECT P.Name, P.Playernr, SUM (F.Amount)
FROM FINES F
INNER JOIN PLAYERS P
ON F.Playernr = P.Playernr
GROUP BY  P.Name,P.Playernr

answered Nov 12, 2013 at 12:52

valex

24.2k7 gold badges47 silver badges62 bronze badges

Jason Heo · Accepted Answer · 2013-11-12 13:00:30Z

0

When GROUP BY used, only group by column and aggregation function can be projected. Remove P.Name in projection column or add P.Name in GROUP BY

SELECT P.Name, P.Playernr, SUM (F.Amount)
FROM FINES F
INNER JOIN PLAYERS P
ON F.Playernr = P.Playernr
GROUP BY  P.Playernr, P.Name

In other RDBMS, your query produces error.

think about below data:

P.Playernr   P.Name
A         B
A         C
A         D

then, P.Name is not in GROUP BY, which value is produced in SELECT P.Playernr. so, you must use aggregation function or add P.Name in GROUP BY

answered Nov 12, 2013 at 12:53

Jason Heo

10.3k2 gold badges39 silver badges72 bronze badges