?
Solved

getting a sum

Posted on 2005-04-22
13
Medium Priority
?
194 Views
Last Modified: 2012-05-05
How do I get a total of one column? The only thing I want displayed is the total of all data from 1 column. Also, the column is varchar. TIA
0
Comment
Question by:Mike Miller
13 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 13843881
Hi mwmiller78,

Well as long as all the data in the column is numeric you can do

Select Sum(Cast MyField As Integer) As Total From MyTable

Again, if you have decimals there you need to change the cast

Tim Cottee
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13843889
Try this:

SELECT SUM(CAST(YourColumn AS INT)) AS YourColumn
FROM YourTable
0
 
LVL 11

Expert Comment

by:lluthien
ID: 13843921
are there numbers in this column?
or do you want a concatinated string?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Mike Miller
ID: 13843931
sorry, yes it's all numbers. there are decimals also. i suppose i would need to cut off everything after the second digit to the right of the decimal as well.
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13843943
SELECT SUM(CAST(YourColumn AS DECIMAL(10, 2))) AS YourColumn
FROM YourTable
0
 

Author Comment

by:Mike Miller
ID: 13844311
Here's what I'm trying...
SELECT SUM(CAST(cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) - cast(Main.Cost as float) * CAST(Main.Quantity as float) AS DECIMAL(10, 2))) AS NetTotal
FROM (cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) - cast(Main.Cost as float) * CAST(Main.Quantity as float) AS DECIMAL(10, 2))) AS NetTotal

I'm getting aritmatic overflow converting datatype to numeric

0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13844329
Try this to identify the row that's causing the problem

SELECT *
FROM mMTownStock
WHERE ISNUMERIC(Cost) = 0 OR
           ISNUMERIC(Quantity) = 0
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13844336
SELECT *
FROM mMTownStock
WHERE (ISNUMERIC(Cost) = 0 AND Cost IS NOT NULL AND Cost != '') OR
            (ISNUMERIC(Quantity) = 0 AND Quantity IS NOT NULL AND Quantity != '')
0
 

Author Comment

by:Mike Miller
ID: 13844363
it came back with no records
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13844390
Do the same on the Main table:

SELECT *
FROM Main
WHERE (ISNUMERIC(Cost) = 0 AND Cost IS NOT NULL AND Cost != '') OR
            (ISNUMERIC(Quantity) = 0 AND Quantity IS NOT NULL AND Quantity != '')
0
 

Author Comment

by:Mike Miller
ID: 13844400
same thing
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 1000 total points
ID: 13844431
Try changing the DECIMAL(10, 2) to float first and see if the error will go away.  If the error goes away, take note of the number of digits of the output and maybe you have to increase the DECIMAL(10,2) to whatever the result will be, like DECIMAL(18,2):

SELECT SUM(CAST(cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) - cast(Main.Cost as float) * CAST(Main.Quantity as float) AS FLOAT)) AS NetTotal
FROM (cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) - cast(Main.Cost as float) * CAST(Main.Quantity as float) AS FLOAT)) AS NetTotal
0
 

Author Comment

by:Mike Miller
ID: 13844469
thats what it was!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question