summing to a total 1

[webron]

country
----------
countryid
pooleid
countryname

pool
------------
pooleid
poulename

game
------------
gameid
pooleid
homecountyid
awaycountyid

prediction
------------
predictionid
userid
gameid
scorehome
scoreaway

predictedscores
---------------
userid
gameid
countryid
score (0, 1, or 3)

I'm working on a footballpool and need a query that gives me the
1. the countrynames
2. sum of the predicted scores as a ranking
for a certain user for a certain pool based on his / hers predictions.

this is what i got sofar:

SELECT	c1.countryname AS homecountry, 
	c2.countryname AS awaycountry, 
	p.pouleid
FROM	country AS c1 
INNER JOIN game AS g ON c1.countryid = g.homecountyid 
INNER JOIN country AS c2 ON g.awaycountryid = c2.countryid 
INNER JOIN predictedscores AS v ON g.gameid = v.gameid 
INNER JOIN pool AS p ON g.poolid = p.poolid
INNER JOIN user AS U ON v.userid = u.userid
and p.pouleid = 1
and u.userid = 1

all i need to now is somehow sum the score field (from predictedscores) for each coutry.

The question is ... how?

 

[vongrunt]

There are few ambiguous details in that model and requirements... sample data + results you want would be nice.

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[webron]

Let's take groupid 1:

matches	[countryid]			my predicted scores	gameid
----------------------  		-------------------
[1]germany	- [2] Costa Rica	6 - 0			1
[3] poland	- [4] Ecuador		1 - 2			2
[1] germany	- [3] poland		2 - 1			3
[4] Ecuador	- [2] Costa Rica	0 - 0			4
[4] Ecuador 	- [1] germany		2 - 3			5
[2] Costa Rica 	- [3]poland		1 - 1			6

predictedscore table would be something like

userid gameid countryid score
------ ------ --------- -----
1 1 1 3
1 1 2 0
1 2 3 0
1 2 4 3
1 3 1 3
1 3 3 0
1 4 4 1
1 4 2 1
1 5 4 0
1 5 1 3
1 6 2 1
1 6 3 1


result should be:
countryname totalscore
----------- ----------
germany 9
Costa Rica 2
poland 1
Ecuador 4

 

[vongrunt]

Then...

select C.countryname, PTS.totalscore
from country C
inner join
(	select PS.countryid, sum(PS.score) as totalscore
	from predictedscores PS
	inner join game G on G.gameid = PS.gameid
	where PS.userid = 1
		and G.pooleid = 1
	group by PS.countryid
) PTS
on PTS.countryid = C.countryid
-- orcder by C.countryid

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[webron]

Perfect! Thank you
Could you be bothered explaining how this all works?

 

[vongrunt]

Query between INNER JOIN( and )PTS returns countries from pool 1 and total predictions for user 1. Run it separately to see how it works.

When viewed from "outside", this query behaves like table named/aliased PTS. That construct is called "derived table" or "inline view". The rest is trivial - you join on country table to lookup for country names

Technically speaking derived table is not necessary in this case:

select PS.countryid[!], C.countryname[/!], sum(PS.score) as totalscore
from predictedscores PS
inner join game G on G.gameid = PS.gameid
inner join country C on PS.countryid = C.countryid
where PS.userid = 1
     and G.pooleid = 1
group by PS.countryid[!], C.countryname[/!]
order by PS.countryid

That's what most of people do - join on everything then GROUP BY on everything SELECTed. Personally I don't like it because code is redundant (red stuff) and not extensible. If any serious change is needed in a query, derived table will make 'em easier.

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]