Using SUM() with multiple tables in sql

Question

I am trying to write a query that will use the sum function to add up all values in 1 column then divide by the count of tuples in another table. For some reason when i run the sum query by itself i get the correct number back but when i use it in my query below the value is wrong.

this is what im trying to do but the numbers are coming out wrong.

select (sum(adonated) / count(p.pid)) as "Amount donated per Child"
from tsponsors s, player p;

I found out the issue is in the sum. below returns 650,000 when it should return 25000

select (sum(adonated)) as "Amount donated per Child"
from tsponsors s, player p;

if i remove the from player p it gets the correct amount. However i need the player table to get the number of players.

I have 3 tables that are related to this query. player(pid, tid(fk)) team(tid) tsponsors(tid(fk), adonated, sid(fk)) this is a joining table

what i want to get is the sum of all the amounts donated to each team sum(adonated) and divide this by the number of players in the database count(pid).

Assuming tables tsponsors and player are linked in some way, you need to put that into your query. For example: WHERE s.playerID=p.playerID — David Starkey
– David Starkey, Commented Apr 15, 2013 at 17:17
Table structure would help: in addition when doing sums for values which are in one to many relationships must be done before the join otherwise you end up with inflated/deflated values. — xQbert
– xQbert, Commented Apr 15, 2013 at 17:37

Francis P · Accepted Answer · 2013-04-15 19:36:37Z

1

I guess your sponsors are giving amounts to teams. You then want to know the proportion of donations per child in the sponsored team.

You would then need something like this:

SELECT p.tid,(SUM(COALESCE(s.adonated,0)) / COUNT(p.pid)) AS "Amount donated per Child"
FROM player p 
LEFT OUTER JOIN tsponsors s ON s.tid=p.tid
GROUP BY p.tid

I also used a LEFT OUTER JOIN in order to show 0$ if a team has no sponsors.

answered Apr 15, 2013 at 19:36

Francis P

13.7k3 gold badges30 silver badges52 bronze badges

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

1 Comment

Jeremy Over a year ago

Thanks this worked perfect I didnt need the outer join though since all teams must have a sponsor.

a1ex07 · Accepted Answer · 2013-04-15 19:22:30Z

1

Try

select sum(s.adonated) / (SELECT count(p.pid) FROM player p) 
 as "Amount donated per Child"
from tsponsors s;

Your original query joins 2 tables without any condition, which results in cross join.

UPDATE

SELECT ts.tid, SUM(ts.adonated),num_plyr
FROM tsponsors ts 
INNER JOIN 
 (
   SELECT tid, COUNT(pid) as num_plyr
   FROM player
   GROUP BY tid
)a ON (a.tid = ts.tid)
GROUP BY ts.tid,num_plyr

edited Apr 15, 2013 at 19:22

answered Apr 15, 2013 at 17:17

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

5 Comments

Jeremy Over a year ago

orginally i had select (sum(adonated)) as "Amount donated per Child" from tsponsors s, player p, team t where s.tid = t.tid and t.tid = p.tid;

Jeremy Over a year ago

my tables are organized where teams contain many players and teams have many sponsors. So i tried joining on the player's team tid with teams tid and teams tid with sponsors tid. still got me an inflated number i think im doing the join wrong.

a1ex07 Over a year ago

@Jeremy: Then I believe you need to put table structure in your question and what you want to achieve. It's impossible to guess what you had initially unless you say it...

Jeremy Over a year ago

i added the structure of the tables how they are linked and what i want from my query.

Jeremy Over a year ago

the above query doesnt work i get an error saying not a single-group group function.

Collectives™ on Stack Overflow

Using SUM() with multiple tables in sql

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related