GROUP BY ElectionName and ROLLUP By TotalCount

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

GROUP BY ElectionName and ROLLUP By TotalCount

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2014-03-19 : 14:08:46

Hello GURUS,

The code below displays the correct results in terms of number but I have two issues.

1, the query is displaying duplicate results
2, I would like to show ElectionName only ones and then positions and candidates associated with each can displayed multiple times under each ElectionName.

Can you please see what I am missing?

Thank you very much.


select [ElectionName] = CASE WHEN GROUPING(p.positionId)= 0 THEN MAX  (p.Position)  ELSE (e.ElectionName + CAST(e.ClosingDate AS VARCHAR(12))) END, 
  p.position, 
  c.candidateName, 
  COUNT(*) 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 e.ElectionName,candidatename, e.closingDate,position,p.PositionID WITH ROLLUP
order by position asc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources