Grouping Results by ElectionName

samflex

Hello, The following code returns the results we want. However, they are being repeated. For instance, we have ElectionName, Position, CandidateName and TotalCount. Rather then display the results in the following format (current format)

ElectionName Position Candidate Name TotCount

Election Name 1 Mar 19 2014 President Candidate Name 1 13
Election Name 1 Mar 19 2014 President Candidate Name 2 22
Election Name 2 Mar 19 2014 President Candidate Name 1 13
Election Name 1 Mar 19 2014 President Candidate Name 2 22
Election Name 1 Mar 19 2014 President Candidate Name 1 13
Election Name 1 Mar 19 2014 President Candidate Name 2 22
Election Name 2 Mar 19 2014 1ST Vice President Candidate VP 1 27
Election Name 2 Mar 19 2014 1ST Vice President Candidate VP 8
Election Name 2 Mar 19 2014 1ST Vice President Candidate VP 27
Election Name 2 Mar 19 2014 1ST Vice President Candidate VP 8
Election Name 3 Mar 19 2014 1ST Vice President Candidate VP 27
etc
etc

We would like the results displayed in the following format:

ElectionName Position Candidate Name TotCount

Election Name 1 Mar 19 2014 President Candidate Name 1 13
President Candidate Name 2 22
President Candidate Name 1 13
President Candidate Name 2 22
President Candidate Name 1 13
President Candidate Name 2 22
Election Name 1 Mar 19 2014 1ST Vice President Candidate VP 1 27
1ST Vice President Candidate VP 1 8
1ST Vice President Candidate VP 1 27
1ST Vice President Candidate VP 1 8
1ST Vice President Candidate VP 1 27
etc
etc

with related positions group together with ElectioName. Below is my code. Any help is greatly appreciated.

Select (e.ElectionName + CAST(e.ClosingDate AS VARCHAR(12))) as [ElectionName],
p.position,
c.candidateName,
COUNT(*) AS TotalCount
from ElectionResults er
inner join candidates c on er.candidateId = c.candidateid
inner join Positions p on er.positionId = p.positionId
inner join Elections e on c.PositionId = c.PositionId
group by grouping Sets(e. ElectionName),p.position,p.positionid,c.candidateName, e.closingDate
order by p.PositionId

Jorgen Andersson

This can be achieved by looping through the resultset and clear the ElectionName if the Position is the same as in the previous row. But this doesn't belong to the database.

Wrong is evil and must be defeated. - Jeff Ello[^]

Mycroft Holmes

As Jorgen has said, this is the wrong tool for the job, the database serves up the data, your UI/Report/Consumer of the data formats that data according you your requirements. So do your loop/delete in the client, not the database.

Never underestimate the power of human stupidity RAH

samflex

I was not needing for app. The code below is what I used for my app. It is doing the grouping by electionName but I can't figure out how to count the total count for each candidate. Can you guys add that bit? I just to get the total scores for each candidateName

WITH
ctePreAgg AS
(
SELECT (ElectionName + ' - ' + CAST(ClosingDate AS VARCHAR(12))) electionName, CASE WHEN Position='Member' THEN '' ELSE Position END As Position, CASE WHEN c.CurrentOfficeHolder='Incumbent' THEN CandidateName + '('+ c.CurrentOfficeHolder + ')' ELSE CandidateName END As CandidateName , c.PositionId,COUNT(*) TotalVotes
FROM Candidates c
JOIN Positions p ON c.PositionId = p.PositionId
JOIN Elections e on c.ElectionId = e.ElectionId
WHERE c.ElectionId IN (1,2,3)
GROUP BY Position, CandidateId, CandidateName,c.PositionId,CurrentOfficeHolder,AnswerType,ElectionName, ClosingDate
)

SELECT [OrgName] = CASE WHEN GROUPING(mh.PositionId) = 0 THEN MAX(mh.Position) ELSE mh.ElectionName END
,Names = CASE WHEN GROUPING(mh.PositionId) = 0 THEN MAX(mh.CandidateName) ELSE '' END
,PositionId = CASE WHEN GROUPING(mh.PositionId) = 0 THEN mh.PositionId ELSE '' END
,TotalCount = CASE WHEN GROUPING(mh.PositionId) = 0 THEN COUNT(*) ELSE '' END
FROM ctePreAgg mh
GROUP BY ElectionName,PositionId WITH ROLLUP
HAVING GROUPING(mh.ElectionName) = 0
ORDER BY mh.ElectionName, GROUPING(mh.PositionId) DESC, mh.PositionID;

Mycroft Holmes

Well you are doing exactly what we advised you not to do and you want suggestion on how to improve doing that. Getting the total/count per group IS a database thing, formatting the text is NOT. Personally I would use ROW_NUMBER and PARTITION OVER but not with a CTE.

Never underestimate the power of human stupidity RAH

samflex

Ok, sorry for my stupidity but I thought this is a database forum. If it is, I am only trying to figure out how to get Total Count for each candidate, not formatting request.