Total of columns

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".

SELECT Sum([MyColumn]) FROM MyTable

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

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.

Try this and see if it addresses your issue:

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

All of my fields are VARCHAR

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

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

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.

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?

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)

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.