Link to home
Start Free TrialLog in
Avatar of Camillia
CamilliaFlag for United States of America

asked on

Two tables - Sum of values - What is the difference

This is SQL 2014

1. I'm using sum to add up a column of values. The column is decimal(18,5)

I run this SQL

select invoicenumber, sum(commission)
from LoadsCommissionExtensions
where InvoiceNumber = 7807
group by InvoiceNumber

and get 37883.00000

1195.11
22267.65
164.76
168.57
312.61
819.47
983.36
983.36
983.36
163.92
4988.99
4817.74
33.63

Open in new window


2. I have the same data in another table but in this table,  The column is decimal(18.2)

I run this SQL
select sum(invoiceAmount) from CommissionInvoices where InvoiceNumber = 7807

and get 37882.53. This is the correct one

1195.11
22267.65
164.76
168.57
312.61
819.47
983.36
983.36
983.36
163.92
4988.99
4817.74
33.63

Open in new window


Is the difference because of the definition of the columns?
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Yes.  decimal(18,5) means 18 digits total (precision) and five to the right of the decimal (scale), which results in the values with rounding returned.

Unless otherwise specified using a CAST or CONVERT function, the data type of the SUM will reflect the data type of the column being summed.
No it is not because of datatype, On my machine I am getting this.

in case of DECIMAL(18,2) - you should get 37882.53.

in case of DECIMAL(18,5) - you should get 37882.53000

By default, SQL Server uses rounding when converting a number to a decimal or numeric value with a lower precision and scale.
HI Camilla,

I suspect that SQL is adding the values correctly and that the issue is in the rendering.

Try recasting the value before the addition.
select invoicenumber, sum(cast (commission as decimal(18,2)))
from LoadsCommissionExtensions
where InvoiceNumber = 7807
group by InvoiceNumber

and

select invoicenumber, sum(cast (commission as decimal(18,5)))
from LoadsCommissionExtensions
where InvoiceNumber = 7807
group by InvoiceNumber
Avatar of Camillia

ASKER

I tried both of these and I still get 37883.00

select invoicenumber, sum(cast(commission as decimal(18,2)))
from LoadsCommissionExtensions
where InvoiceNumber = 7807
group by InvoiceNumber

select invoicenumber, cast(sum(commission) as decimal(18,2))
from LoadsCommissionExtensions
where InvoiceNumber = 7807
group by InvoiceNumber

Open in new window

What is the data type of commission column. I think it is not DECIMAL(18,2)
kdo,

I tried your SQL as well and still get 37883.00

Type of commission is Decimal (18,5)

The other table that gets the correct result is decimal (18,2)
SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America 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
What you executed are same statements only. :)

So you are bound to get the same 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
Can you please post you table schema with datatypes ?
They're both tables. Let me see the data. These are the table schemas.

I'll check the data and post back

CREATE TABLE [dbo].[LoadsCommissionExtensions](
	[LoadsCommissionExtensionId] [int] IDENTITY(1,1) NOT NULL,
	[CommSettledSolids] [decimal](18, 2) NULL,
	[CommCtsPerLbCwt] [decimal](18, 5) NULL,
	[Commission] [decimal](18, 5) NULL,
	[CommEstimated] [nvarchar](max) NULL,
	[CommissionError] [nvarchar](max) NULL,
	[InvoiceNumber] [int] NULL,
	[CreateDate] [datetime] NOT NULL,
	[UpdateDate] [datetime] NULL,
	[CommSalesOrder_CommSalesOrderId] [int] NULL,
	[CreateUser_Id] [nvarchar](128) NULL,
	[Load_LoadId] [int] NULL,
	[UpdateUser_Id] [nvarchar](128) NULL,
	[LoadNumFromOrigLCE] [int] NULL,
	[ShipDateFromOrigLCE] [datetime] NULL,
 CONSTRAINT [PK_dbo.LoadsCommissionExtensions] PRIMARY KEY CLUSTERED 
(
	[LoadsCommissionExtensionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


CREATE TABLE [dbo].[CommissionInvoices](
	[CommissionInvoicesId] [int] IDENTITY(1,1) NOT NULL,
	[InvoiceAmount] [decimal](18, 2) NOT NULL,
	[SettledWeight] [int] NOT NULL,
	[SolomonFlag] [bit] NULL,
	[Status] [int] NOT NULL,
	[InvoiceCancelled] [bit] NULL,
	[InvoiceNumber] [int] NOT NULL,
	[CreateDate] [datetime] NOT NULL,
	[UpdateDate] [datetime] NULL,
	[CommSalesOrder_CommSalesOrderId] [int] NULL,
	[CreateUser_Id] [nvarchar](128) NULL,
	[InvoicedTo_AccountId] [int] NOT NULL,
	[UpdateUser_Id] [nvarchar](128) NULL,
 CONSTRAINT [PK_dbo.CommissionInvoices] PRIMARY KEY CLUSTERED 
(
	[CommissionInvoicesId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Open in new window

The Commission column has decimal but it's 232 rows. Anyway to double check it without eyeballing it...just in case I missed something?

149.39000
149.39000
149.39000
149.39000
149.39000
149.39000
149.39000
149.39000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.95000
164.76000
168.57000
156.30000
156.30000
163.89000
163.89000
163.89000
163.89000
163.89000
163.89000
163.89000
163.89000
163.89000
163.89000
163.89000
163.89000
163.89000
163.89000
163.89000
163.89000
163.89000
163.89000
163.89000
163.89000
163.89000
163.89000
163.89000
163.91000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.78000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
146.73000
185.30000
185.30000
185.30000
185.30000
185.30000
185.30000
185.30000
185.30000
185.30000
185.30000
185.30000
185.30000
185.30000
185.30000
185.32000
185.30000
185.30000
185.30000
185.30000
185.30000
185.30000
185.30000
185.30000
185.30000
185.30000
185.30000
33.63000

Open in new window

If I change the commission column to decimal(18,2), would that fix it? or the other table to decimal(18,5)....

It has to be consistent
Yes it should fix it. Try it out once.At both places cast and then sum.

SUM(CAST(col as DECIMAL(18,2)))
You could go down that road, but I'm guessing there's a reason commissions are a decimal (18,5) depending on the nuttiness of your company's commission structure, especially if it can be multi-levels deep with agents getting a 'parent' commission for their 'child' agents, or some clients with multiple agents receiving comissions.

Never underestimate the volume at which someone getting paid on commission will scream bloody murder if their paychecks are off.

Tread VERY carefully.
You're right, Jim.  I won't change it.

I tried this but still the same values

select invoicenumber, SUM(CAST(Commission as DECIMAL(18,2)))
from LoadsCommissionExtensions
where InvoiceNumber = 7807
group by InvoiceNumber
----37883.00

select SUM(CAST(InvoiceAmount as DECIMAL(18,2))) from CommissionInvoices where InvoiceNumber = 7807
--- 37882.53

Open in new window

I get the data in CommissionInvoices from our old database table. I doubt that has anything to do with it but not sure. This is out the data is in the old database's table

1195.11
22267.65
164.76
168.57
312.61
819.47
983.36
983.36
983.36
163.92
4988.99
4817.74
33.63

Open in new window

Ok so got it.. You summation is coming to 37883.0000000001.

An in DECIMAL(18,5) we get 37883.00000.


Try this.

SELECT SUM(CAST (ID AS FLOAT)) FROM testFloats11



Output

-------------------


(No column name)
37883.0000000001
Script for you

CREATE TABLE testFloats11
(
	ID DECIMAL(18,5)
) 
GO

INSERT INTO testFloats11 VALUES
(149.39),
(149.39),
(149.39),
(149.39),
(149.39),
(149.39),
(149.39),
(149.39),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.95),
(164.76),
(168.57),
(156.3),
(156.3),
(163.89),
(163.89),
(163.89),
(163.89),
(163.89),
(163.89),
(163.89),
(163.89),
(163.89),
(163.89),
(163.89),
(163.89),
(163.89),
(163.89),
(163.89),
(163.89),
(163.89),
(163.89),
(163.89),
(163.89),
(163.89),
(163.89),
(163.89),
(163.91),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.78),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(146.73),
(185.3),
(185.3),
(185.3),
(185.3),
(185.3),
(185.3),
(185.3),
(185.3),
(185.3),
(185.3),
(185.3),
(185.3),
(185.3),
(185.3),
(185.32),
(185.3),
(185.3),
(185.3),
(185.3),
(185.3),
(185.3),
(185.3),
(185.3),
(185.3),
(185.3),
(185.3),
(33.63)

Open in new window



Query used.

SELECT SUM(CAST (ID AS FLOAT)) FROM testFloats11

Open in new window


Enjoy !!
where do you see 37883.0000000001 ?

Let me see, i just saw your second post
I see it now! Going to ask my manager who's a SQL developer what he wants to do... so still...how to fix this.... would changing decimal(18,5) change to decimal(18,2) fix it? ( I know not a good solution because this is commission)
Great !  you can ask , there are lot of options available.
My manager who's a SQL Developer says to change it to decimal(18,2) because that's how the old system is...old system is numeric(10,2)...it's SQL 2005. He said to change it and compare and see if that fixes it.

Now, did the xxxx.0000000001 in  37883.0000000001 cause it to be rounded up to 37883.00?
Great !! finally you have a go ahead.
yes you r right.

Enjoy !!
HI Camilla,

Don't change anything yet!

The long list of values with 5 decimal places does add up to an integer value!  (37883)

That would suggest that the values are not the same in the two tables!


Kent
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
Here are the details....how you can check the differences. ....

--Here are the difference in the 2 tables

--Compare the value in first table ----> 1195.11

--Check the value in 2nd table
SELECT (149.39)+
            (149.39)+
            (149.39)+
            (149.39)+
            (149.39)+
            (149.39)+ ---------------- > 1195.12
            (149.39)+
            (149.39)

--Compare the value in first table ----> 312.61

--Check the value in 2nd table
SELECT (156.3) + (156.3) ----------------> 312.6
hmm...ok let me double check
Yes basically we are doing sum twice.

one from the smaller values --- > to a group by sum --> then sum of all the values present in the group.
I don't know. I'll check..going back to work soon and see.
Yes Sure, Pls update us when you are back.