Camillia
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
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
Is the difference because of the definition of the columns?
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
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
Is the difference because of the definition of the columns?
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.
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
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
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
What is the data type of commission column. I think it is not DECIMAL(18,2)
ASKER
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)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What you executed are same statements only. :)
So you are bound to get the same value.
So you are bound to get the same value.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you please post you table schema with datatypes ?
ASKER
They're both tables. Let me see the data. These are the table schemas.
I'll check the data and post back
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
ASKER
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
ASKER
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
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)))
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.
Never underestimate the volume at which someone getting paid on commission will scream bloody murder if their paychecks are off.
Tread VERY carefully.
ASKER
You're right, Jim. I won't change it.
I tried this but still the same values
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
ASKER
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
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
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
Query used.
Enjoy !!
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)
Query used.
SELECT SUM(CAST (ID AS FLOAT)) FROM testFloats11
Enjoy !!
ASKER
where do you see 37883.0000000001 ?
Let me see, i just saw your second post
Let me see, i just saw your second post
ASKER
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.
ASKER
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?
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 !!
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
--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
ASKER
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.
one from the smaller values --- > to a group by sum --> then sum of all the values present in the group.
ASKER
I don't know. I'll check..going back to work soon and see.
Yes Sure, Pls update us when you are back.
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.