February 12, 2014 at 4:44 am
Hi,
I'm using this query to sum the values. The cost column is a float datatype and what could I differently do here to sum the cost. I'm unable to sum the cost.
Also is there any way I change the datatype to int for Cost column without losing the data.
select ID, MAX(Date) Date, SUM(Cost) Cost, MAX(Funding) Funding from Appllication
group by ID
February 12, 2014 at 5:15 am
What do you mean by "I'm unable to sum the cost."? SUM can be applied to float.
As float are not exact datatype it is a bad choice for such calculations and keeping monetary values.
If all of values in your float column represent a whole numbers within int range, converting it to INT should not cause lose of data. However, it's very rare (otherwise why it is a float in first case?). If you have values with decimals you will be better to convert them to DECIMAL datatype.
February 12, 2014 at 5:39 am
I changed the datatype to int. But now when I run the query It says invalid column name for cost.
Table Schema:
CREATE TABLE [dbo].[Application](
[ID] [int] NOT NULL,
[Date] [date] NULL,
[ Cost] [int] NULL,
[Funding] [varchar](5) NOT NULL
) ON [PRIMARY]
GO
February 12, 2014 at 5:53 am
Looks like you have a space at front of the Cost, so yuor full column name is [ Cost]. Rename it to remove the space
February 12, 2014 at 6:10 am
I am using the below query and doesn't work
SELECT LTRIM(Cost) FROM Application
February 12, 2014 at 6:24 am
You are trying to TRIM the value in the column which doesn't exist!
You or someone else was not very accurate while changing table definition and added space to the name of the column. Check what you have posted previously:
CREATE TABLE [dbo].[Application](
[ID] [int] NOT NULL,
[Date] [date] NULL,
[ Cost] [int] NULL,
[Funding] [varchar](5) NOT NULL
) ON [PRIMARY]
can you see a space? [ Cost] should be changed to [Cost]
Otherwise, you always need to use square brakets when referring to this column. In your case the following will work:
SELECT [ Cost] FROM dbo.Application
February 12, 2014 at 6:58 am
Yeah it worked. Thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply