Calculating Percentage on the same column 1

[Ant0]

I have two tables, results and constits. In Constits I have area, in results I have party and votes. So results has, for each party the number of votes cast, and further to this an id col in results links it to constits.

I want to see:

SELECT constist.area, results.party, party.votes, (number_of_votes_for_party/total_number_of_votes_for_area)
FROM constits c
INNER JOIN results r
ON c.id = r.id

I am seriously struggling to work this out. I can return the votes per party and total votes per area separatly but I don't know to have both in one query so I can perform the maths.

Thanks

 

[JarlH]

[tt]SELECT constist.area,
results.party,
party.votes,
(SELECT SUM(party_votes) FROM results
WHERE results.party = r.party)*1.0 /
(SELECT SUM(party_votes) FROM results)*1.0
FROM constits c
INNER JOIN results r
ON c.id = r.id[/tt]

 

[JarlH]

Sorry about my answer above, wasn't meant to post it but pushed "submit" by accident (instead of "preview").

 

[JarlH]

Is there really a column party.votes? (I can't find the party table...) Or do you mean party_votes, as a column in the results table? (That's what I've assumed.)

Well, my answer above does probably work (if I got you right).

But it can be somewhat simplified:
[tt]SELECT constist.area,
results.party,
party.votes,
party_votes*1.0 /
(SELECT SUM(party_votes) FROM results
WHERE results.id = r.id)*1.0
FROM constits c
INNER JOIN results r
ON c.id = r.id[/tt]

 

[Ant0]

JarlH, thank you very much! This has helped me to solve my problem!!!

In answer to your question, that is my typo it should have been results.votes not party.votes. Either way your info helped me.

Now can I ask one more question..... I don't completely get it!!! I know this is terrible, but would you be able to explain it to me?