|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Generate data entry and reporting .NET Web apps in minutes, straight from your database. Read our FREE whitepaper “Build Web 2.0 Applications Without Hand-Coding” Download now! |
|
#1
February 28th, 2004, 07:09 PM
|
|||
|
|||
SUM function
Hi,
I am trying to write a stored procedure that would return a percentage vote instead of numerical. The db contains five fields: first name, last name, votes, office, class. It would be an easy procedure if not the last two, because sum function suppose to add votes of candidates only if they are similar office and class. Is there an easy way out of this mess. I can write a function from asp.net but it would mean that I have to connect to db over and over. Not very efficient. I tried GROUP BY but had no luck. Thanks, Oskar CREATE PROCEDURE Results AS DECLARE @TotalVotes FLOAT SELECT @TotalVotes = SUM(votes) FROM Candidates GROUP BY office, class IF NOT @TotalVotes = 0 SELECT firstname, lastname, ROUND((votes*100)/@TotalVotes,1) AS votes FROM Candidates ORDER BY votes DESC ELSE SELECT firstname, lastname, votes FROM Candidates ORDER BY votes DESC GO
|
|
#2
February 28th, 2004, 07:54 PM
|
||||
|
||||
|
use a correlated subquery:
Code:
select firstname
, lastname
, round(
(votes*100)
/(select sum(votes)
from candidates
where office = X.office
and class = X.class)
,1) as votes
from candidates X
|
|
#4
February 28th, 2004, 11:22 PM
|
||||
|
||||
|
Code:
select firstname
, lastname
, case when 0 =
(select sum(votes)
from candidates
where office = X.office
and class = X.class)
then 0 else
round(
(votes*100)
/(select sum(votes)
from candidates
where office = X.office
and class = X.class)
,1)
end as votes
from candidates X
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > SUM function |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
