Link to home
Start Free TrialLog in
Avatar of garethtnash
garethtnashFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL Server - Sum Decimal Columns

Hello Experts,

I have a table that contains multiple DECIMAL columns - (Hours worked) I'm trying to combine these into just two columns - (Normal Hours worked, Overtime Hours worked) using an update statement, with SUM

It's not working? What am I missing here ? is it not possible to SUM DECIMAL values?

Here's my code -

CREATE PROCEDURE [dbo].[Test Decimal Proc] 
AS
SET NOCOUNT ON;
BEGIN
	BEGIN
	CREATE TABLE #Ratecard(
		[ID] [int] IDENTITY(1,1) NOT NULL,
		[DSHours] [decimal](6, 2) NULL,
		[BSHours] [decimal](6, 2) NULL,
		[NSHours] [decimal](6, 2) NULL,
		[WDOTHors] [decimal](6, 2) NULL,
		[WEOTHours] [decimal](6, 2) NULL
	) ON [PRIMARY]
	END
	BEGIN
	INSERT #Ratecard (DSHours, BSHours, NSHours, WDOTHors, WEOTHours) VALUES (30.00, 5.00, 0.00, 0.00, 2.00)
	INSERT #Ratecard (DSHours, BSHours, NSHours, WDOTHors, WEOTHours) VALUES (30.00, 0.00, 10.00, 8.00, 2.00)
	INSERT #Ratecard (DSHours, BSHours, NSHours, WDOTHors, WEOTHours) VALUES (30.00, 5.00, 3.00, 5.00, 2.00)
	INSERT #Ratecard (DSHours, BSHours, NSHours, WDOTHors, WEOTHours) VALUES (10.00, 5.00, 8.00, 15.00, 2.00)
	END
	BEGIN
	ALTER TABLE #Ratecard
	ADD  [Conditional Hrs] DECIMAL (5,2) NULL, [OT Hrs] DECIMAL (5,2) NULL
	END
	BEGIN
	Update #Ratecard set [Conditional Hrs] = SUM(DSHours + BSHours + NSHours)
	Update #Ratecard set [OT Hrs] = SUM(WDOTHors + WEOTHours)
	END
	BEGIN
	Select * from #Ratecard
	END
END

Open in new window



Here's what happens when I execute -

Msg 157, Level 15, State 1, Procedure Test Decimal Proc, Line 27
An aggregate may not appear in the set list of an UPDATE statement.

Any suggestions?

Thank you
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

You might want to create COMPUTED columns instead?
You also don't need the BEGIN..END blocks:
CREATE PROCEDURE [dbo].[Test Decimal Proc] 
AS
SET NOCOUNT ON;
CREATE TABLE #Ratecard(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[DSHours] [decimal](6, 2) NULL,
	[BSHours] [decimal](6, 2) NULL,
	[NSHours] [decimal](6, 2) NULL,
	[WDOTHors] [decimal](6, 2) NULL,
	[WEOTHours] [decimal](6, 2) NULL,
	[Conditional Hrs] AS DSHours + BSHours + NSHours,
	[OT Hrs] AS WDOTHors + WEOTHours
) ON [PRIMARY]


INSERT #Ratecard (DSHours, BSHours, NSHours, WDOTHors, WEOTHours) VALUES (30.00, 5.00, 0.00, 0.00, 2.00)
INSERT #Ratecard (DSHours, BSHours, NSHours, WDOTHors, WEOTHours) VALUES (30.00, 0.00, 10.00, 8.00, 2.00)
INSERT #Ratecard (DSHours, BSHours, NSHours, WDOTHors, WEOTHours) VALUES (30.00, 5.00, 3.00, 5.00, 2.00)
INSERT #Ratecard (DSHours, BSHours, NSHours, WDOTHors, WEOTHours) VALUES (10.00, 5.00, 8.00, 15.00, 2.00)

Select * from #Ratecard
GO

Open in new window

Please try this ..

Update #Ratecard set [Conditional Hrs] = SUM(DSHours + BSHours + NSHours) OVER()
Update #Ratecard set [OT Hrs] = SUM(WDOTHors + WEOTHours) OVER()

Open in new window

Avatar of garethtnash

ASKER

Hi Both,

So -

Vitor -

Thanks, but, at a later stage i need to delete the DSHours + BSHours + NSHours columns - would I loose the data then?


Pawan - when i try your solution, I get -

Msg 4108, Level 15, State 1, Procedure Test Decimal Proc, Line 28
Windowed functions can only appear in the SELECT or ORDER BY clauses.
When I try this -

ALTER PROCEDURE [dbo].[Test Decimal Proc] 
AS
SET NOCOUNT ON;
BEGIN
	BEGIN
	CREATE TABLE #Ratecard(
		[ID] [int] IDENTITY(1,1) NOT NULL,
		[DSHours] [decimal](6, 2) NULL,
		[BSHours] [decimal](6, 2) NULL,
		[NSHours] [decimal](6, 2) NULL,
		[WDOTHors] [decimal](6, 2) NULL,
		[WEOTHours] [decimal](6, 2) NULL
	) ON [PRIMARY]
	END
	BEGIN
	INSERT #Ratecard (DSHours, BSHours, NSHours, WDOTHors, WEOTHours) VALUES (30.00, 5.00, 0.00, 0.00, 2.00)
	INSERT #Ratecard (DSHours, BSHours, NSHours, WDOTHors, WEOTHours) VALUES (30.00, 0.00, 10.00, 8.00, 2.00)
	INSERT #Ratecard (DSHours, BSHours, NSHours, WDOTHors, WEOTHours) VALUES (30.00, 5.00, 3.00, 5.00, 2.00)
	INSERT #Ratecard (DSHours, BSHours, NSHours, WDOTHors, WEOTHours) VALUES (10.00, 5.00, 8.00, 15.00, 2.00)
	END
	BEGIN
	ALTER TABLE #Ratecard
	ADD  [Conditional Hrs] AS DSHours + BSHours + NSHours, [OT Hrs] AS WDOTHors + WEOTHours
	END
	/*BEGIN
	Update #Ratecard set [Conditional Hrs] = SUM(DSHours + BSHours + NSHours) OVER()
Update #Ratecard set [OT Hrs] = SUM(WDOTHors + WEOTHours) OVER()
	END*/
	BEGIN
	Select * from #Ratecard
	END
	BEGIN
	Alter TABLE #Ratecard
	DROP COLUMN  DSHours, BSHours, NSHours, WDOTHors, WEOTHours
	Select * from #Ratecard
	END
END
GO

Open in new window


I get -

Msg 5074, Level 16, State 1, Procedure Test Decimal Proc, Line 35
The column 'Conditional Hrs' is dependent on column 'DSHours'.
Msg 4922, Level 16, State 9, Procedure Test Decimal Proc, Line 35
ALTER TABLE DROP COLUMN DSHours failed because one or more objects access this column.
SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Update #Ratecard set [Conditional Hrs] = (select SUM(DSHours + BSHours + NSHours) OVER() from #ratecard)

Update #Ratecard set [OT Hrs] = (select SUM(WDOTHors + WEOTHours) OVER() from #ratecard)
HI Guys,

If i use computed columns, can i copy the data from the computed column to a new column as decimal i.e. not copy the formula that computes the value??

Thanks
If i use computed columns, can i copy the data from the computed column to a new column as decimal i.e. not copy the formula that computes the value??
Yes, you can copy the data. The compute is only to say to the engine that column doesn't need to be stored by the user but to use values from other columns to store the value.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Excellent - thank you
Excellent thank you so much...