Solved

# Total of columns

Posted on 2005-04-21
225 Views
I want to get a total of a column.
At the bottom of the column will be the total. How do I do this?

0
Question by:mwmiller78

LVL 5

Expert Comment

The total of a column can be retrieved by

SELECT SUM(<ColName>)
FROM <TableName>

Not sure what you mean by "At the bottom of the column will be the total".
0

LVL 50

Expert Comment

SELECT Sum([MyColumn]) FROM MyTable
0

LVL 28

Expert Comment

To get the result set and the total at the bottom, you can try this:

SELECT YourColumn FROM YourTable
UNION ALL
SELECT SUM(YourColumn) AS YourColumn FROM YourTable
0

Author Comment

at the bottom...

Column1
5
4
3
7
6

25   <-------this would be the total

rafrancisco
I see what you are saying, but in my case, i'm not sure this would work. The column I want to total is not actually a column. Here's my query:

SELECT DISTINCT [Main].PART_NUMBER, [imMTownStock].Quantity as SMFQuantity, [Main].Quantity,
cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) - cast(Main.Cost as float) * CAST(Main.Quantity as float) AS NetChange
From Main LEFT JOIN imMTownStock ON ([Main].PART_NUMBER = [imMTownStock].PartNumber AND [Main].Quantity <> [imMTownStock].Quantity)

The last column (NetChange) I want to have a total from each record of every entry entered in this column.

0

LVL 28

Expert Comment

SELECT PART_Number, SUM(SMFQuantity), SUM(Quantity), SUM(NetChange)
FROM (
SELECT DISTINCT [Main].PART_NUMBER, [imMTownStock].Quantity as SMFQuantity,
[Main].Quantity,
cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) - cast(Main.Cost as float) * CAST(Main.Quantity as float) AS NetChange
From Main LEFT JOIN imMTownStock ON ([Main].PART_NUMBER = [imMTownStock].PartNumber AND [Main].Quantity <> [imMTownStock].Quantity)
) A
GROUP BY Part_Number WITH ROLLUP
0

Author Comment

All of my fields are VARCHAR
0

LVL 28

Expert Comment

Try this one:

SELECT PART_Number, SUM(SMFQuantity), SUM(Quantity), SUM(NetChange)
FROM (
SELECT DISTINCT [Main].PART_NUMBER, CAST([imMTownStock].Quantity  AS INT) as SMFQuantity,
CAST([Main].Quantity AS INT) AS Quantity,
cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) - cast(Main.Cost as float) * CAST(Main.Quantity as float) AS NetChange
From Main LEFT JOIN imMTownStock ON ([Main].PART_NUMBER = [imMTownStock].PartNumber AND [Main].Quantity <> [imMTownStock].Quantity)
) A
GROUP BY Part_Number WITH ROLLUP
0

Author Comment

I'm not sure exactly what that did, but I have way less records now.
0

LVL 28

Expert Comment

The code creates a total of your SMFQuantity, Quantity and NetChange by Part_Number.  Then at the end there's a total for each column by Part_Number.

Try this if you just want the sum for the NetChange:

SELECT PART_Number, SMFQuantity, Quantity, SUM(NetChange)
FROM (
SELECT DISTINCT [Main].PART_NUMBER, [imMTownStock].Quantity as SMFQuantity,
[Main].Quantity,
cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) - cast(Main.Cost as float) * CAST(Main.Quantity as float) AS NetChange
From Main LEFT JOIN imMTownStock ON ([Main].PART_NUMBER = [imMTownStock].PartNumber AND [Main].Quantity <> [imMTownStock].Quantity)
) A
GROUP BY Part_Number, SMFQuantity, Quantity WITH ROLLUP

If this still doesn't address your issue, please provide an example of the expected output containing all columns.
0

LVL 68

Accepted Solution

For example, if col4 is the column you want to total up:

SELECT col1, col2, col3, col4
FROM someTable
WHERE ...
--ORDER BY ...
COMPUTE SUM(col4)
0

Author Comment

the column i want to compute isn't really a column. its a mathematic result of other columns that is displayed as a column. In regards to my query this is what I want to total:

cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) - cast(Main.Cost as float) * CAST(Main.Quantity as float) AS NetChange

I've tried every way I can think of using compute sum...what am I doing wrong?
0

LVL 28

Expert Comment

Using ScottPletcher's suggestion, your query will look like this:

SELECT PART_Number, SMFQuantity, Quantity, NetChange
FROM (
SELECT DISTINCT [Main].PART_NUMBER, [imMTownStock].Quantity as SMFQuantity,
[Main].Quantity,
cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) - cast(Main.Cost as float) * CAST(Main.Quantity as float) AS NetChange
From Main LEFT JOIN imMTownStock ON ([Main].PART_NUMBER = [imMTownStock].PartNumber AND [Main].Quantity <> [imMTownStock].Quantity)
) A
COMPUTE SUM(NetChange)
0

LVL 68

Expert Comment

Thanks!

Btw, I think you could also do this, without using a derived table:

SELECT DISTINCT [Main].PART_NUMBER, [imMTownStock].Quantity as SMFQuantity,
[Main].Quantity,
cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) - cast(Main.Cost as float) * CAST(Main.Quantity as float) AS NetChange
From Main LEFT JOIN imMTownStock ON ([Main].PART_NUMBER = [imMTownStock].PartNumber AND [Main].Quantity <> [imMTownStock].Quantity)
COMPUTE SUM(cast(imMTownStock.Cost as float) * cast(imMTownStock.Quantity as float) - cast(Main.Cost as float) * CAST(Main.Quantity as float))

As long as the *exact* experession is used in the SUM() as was in the SELECT, I think you're OK.
0

## Featured Post

### Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.