Problem with Multiple Summing in Join 1

[sila]

Hi,
I need some help with my sql query below. The results I'm getting back for the Electorate is way higher than it should be, I think its somehow totalling it in both dbo.Candidate and dbo.DivElection.
(dbo.Candidate has DivElecID in it numerous times due to the multiple candidates in a DivElecID)

Any help greatly appreciated, any further explanations needed please let me know. Cheers guys and gals.

Sila

SELECT     ROUND(SUM(dbo.Candidate.VotesReceived) 
	                                          / SUM(dbo.DivElection.Electorate) * 100, 2) AS Percentage, 
	                                          SUM(dbo.Candidate.VotesReceived) AS Turnout, 
	                                          SUM(dbo.DivElection.Electorate) AS Electorate 
	                    FROM         dbo.DivElection inner JOIN
	                                          dbo.Candidate ON dbo.DivElection.DivElecID = dbo.Candidate.DivElecID
	                    WHERE     (dbo.DivElection.ElectionID = @ElectionId)

 

[JamesLean]

Not sure I quite understand the two tables involved. Can you post details of what columns are in each table and the relationship between them?

--James

 

[sila]

Thanks

The tables, CANDIDATES and DIVELECTION are as below:

Primary Key = DivElection.DivElecID
Foreign Key = Candidate.DivElecID

CANDIDATES

1	CandidateID	int	4	0
0	DivElecID	int	4	1
0	ElectionID	int	4	1
0	Surname	varchar	50	1
0	Title	varchar	10	1
0	Initials	varchar	5	1
0	Forename1	varchar	30	1
0	ForeName2	varchar	30	1
0	Forename3	varchar	30	1
0	Address1	varchar	50	1
0	Address2	varchar	50	1
0	Address3	varchar	50	1
0	Address4	varchar	50	1
0	Postcode	varchar	50	1
0	Email	varchar	50	1
0	PartyNameID	int	4	1
0	DivisionID	int	4	1
0	FirstElecDate	smalldatetime	4	1
0	VotesReceived	numeric	9	1
0	Winner	bit	1	1
0	PercentageOfVotes	float	8	1
0	Awards	nvarchar	255	1
0	General	nvarchar	255	1

DIVELECTION

3	DivElecID	int	4	0
0	DivisionID	int	4	0
0	ElectionID	int	4	1
0	Electorate	int	4	1
0	TimeReceived	datetime	8	1
0	PercentTurnout	float	8	1
0	DivTotalVotes	numeric	9	1
0	Comments	varchar	50	1
0	SelectForElec	bit	1	1
0	Completed	bit	1	1
0	ResultsComplete	bit	1	1

 

[JamesLean]

Is this what you want?

SELECT de.divelecid,
  de.electorate,
  SUM(c.votesreceived) AS turnout,
  100.0 * SUM(c.votesreceived) / de.electorate AS percentage
FROM divelection de JOIN candidate c ON de.divelecid = c.divelecid
WHERE de.electionid = @electionid
GROUP BY de.divelecid,
  de.electorate

--James

 

[sila]

Your query brings back:

divelecid   electorate  turnout               percentage            
----------- ----------- --------------------- --------------------- 
2673        12541       7669                  61.151423             
2674        11440       8340                  72.902097             
2675        9130        7151                  78.324205             
2676        11934       7462                  62.527233             
2677        12703       5650                  44.477682             
2678        12831       6117                  47.673602             
2679        11207       7271                  64.879093

but I need it to be totalled to output as below:

Percentage            Turnout               Electorate  
--------------------- --------------------- ----------- 
19.43                 685595                3529325

Its the electorate in mine that is totalling too many fields.

Thanks

 

[JamesLean]

What about this?

SELECT 100.0 * SUM(turnout) / SUM(electorate) AS percentage,
	SUM(turnout) AS turnout,
	SUM(electorate) AS electorate
FROM (
		SELECT de.electorate,
			SUM(c.votesreceived) AS turnout
		FROM divelection de JOIN candidate c ON de.divelecid = c.divelecid
		WHERE de.electionid = @electionid
		GROUP BY de.divelecid,
			de.electorate
	) t

--James

 

[sila]

Thanks alot James, thats exactly it - brilliant!