Sum - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sum

Author

Topic

MarioK
Starting Member

20 Posts

Posted - 2005-01-27 : 14:29:48

Hi all,
I have a , table debtors, column interest
how can i set data type for interest: varchar or decimal?

ex: interest
100.1
100.2
100.3

I expect the result look like this
300.6

I'm using SQl Query Analyer

USE CALLDATA
GO
SELECT interest, SUM(interest) AS total_interest
FROM debtors
Go
Thanks all

Mariok

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-27 : 14:50:32

Mariok,

I would set the datatype to 'float'.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

MarioK
Starting Member

20 Posts

Posted - 2005-01-27 : 15:11:56

after i used data type float for interest then
i got an error
Server: Msg 8118, Level 16, State 1, Line 1
Column 'debtors.Interest' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

please correct for my SQL Query? i think something wrong with the query

thanks semper fi

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-27 : 15:24:23

MarioK

In your code:

SELECT interest, SUM(interest) AS total_interest
FROM debtors
Go

You have a misspelled item. See underlined.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-27 : 15:30:12

quote:
Originally posted by Xerxes

MarioK

In your code:

SELECT interest, SUM(interest) AS total_interest
FROM debtors
Go

You have a misspelled item. See underlined.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

Sorry, I misread.....must be the bifocals...I'll look at this again.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-27 : 15:57:22

quote:
Originally posted by Xerxes

MarioK

In your code:

SELECT interest, SUM(interest) AS total_interest
FROM debtors
Go

You have a misspelled item. See underlined.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

Sorry, I misread.....must be the bifocals...I'll look at this again.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-27 : 16:07:01

USE CALLDATA
GO
SELECT SUM(interest) AS total_interest
FROM debtors
Go

Brett

8-)

EDIT: And don't use float...BOL

quote:

float and real

Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-27 : 16:23:05

Float is not appropriate here. Decimal is best for this. The sample data that you have in your post would be DECIMAL(4,1). Let us know the range of your data and we'll tell you what size to make it.

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-27 : 16:26:33

I stand corrected on that 'float'

remark

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

MarioK
Starting Member

20 Posts

Posted - 2005-01-27 : 16:45:42

Thankx Brett! it's work great.

1) im still want to ask you a Question
when i use float the result 123.0
decimal 123
money 123.0000
varchar error
I want the result look like this: 123.00 please advise

2) another ex: interest
100.1
100.2
100.3
0
NULL
PLEASE SHOW ME THE code FOR THIS, Brett or everyone. tHANKS all

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-27 : 16:48:10

You need to use DECIMAL(x, 2). What x is depends on your data. If you have data like nnnnnn.nn, then you'd want (8, 2). 2 is the right side of the decimal places. To figure out what you need for x, count up both sides of the decimal palce.

Tara

MarioK
Starting Member

20 Posts

Posted - 2005-01-27 : 16:55:11

How can i place this decimal(x.,2) in the code?
my data interest row : 30658.01
total interest : 4316264.00

Thanks Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-27 : 17:05:21

You need to change the data type to DECIMAL(x, 2). You do this on the table and not in your code (unless you are using CONVERT which it doesn't sound like you are). So you can change the data type in Enterprise Manager or via ALTER TABLE in Query Analyzer.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources