September 5, 2008 at 1:26 pm
I am trying to total the amount for each row in my table. I am doing this so I can calculate the percentage for each row compared to the total.
I have a very simple table. I am trying to total the values in the [tally] column.
CREATE TABLE [tallyMarketing] (
[tallyID] [int] IDENTITY (1, 1) NOT NULL ,
[type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[tally] [int] NULL ,
[lastEntry] [datetime] NULL CONSTRAINT [DF_tallyMarketing_lastEntry] DEFAULT (getdate()),
CONSTRAINT [PK_tallyMarketing] PRIMARY KEY CLUSTERED
(
[tallyID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Thank you for your assistance.
Norbert
September 5, 2008 at 1:42 pm
SELECT SUM(Tally) FROM dbo.TallyMarketing
I'm not too sure what you want to do next though...
BTW my first instinct is to tell you that such calculations are often better left off to the front-end application... but there's a way to do this is SQL too.
September 5, 2008 at 2:07 pm
Thank you very much. This is very helpful!
September 8, 2008 at 7:29 am
There are several ways to do this in SQL and depend on if you are using a stored procedure or ad-hoc SQL. In a stored procedure I think the best way to do it is to store the table sum in a variable and then use the variable in a second select like this:
Declare @tally_sum Int
Select
@tally_sum = Sum(tally)
From
dbo.TallyMarketing
Select
tallyID,
tally,
@tally_sum as total,
tally * 1.0/@tally_sum * 100 as pct_of_total
From
dbo.TallyMarketing
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2008 at 11:28 am
Mr. Corbett,
Thank you for this clear and great explanation!
Norbert
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply