Total of columns

I want to get a total of a column.
At the bottom of the column will be the total. How do I do this?

Mike MillerSoftware EngineerAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
obahatCommented:
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
 
Steve BinkCommented:
SELECT Sum([MyColumn]) FROM MyTable
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rafranciscoCommented:
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
 
Mike MillerSoftware EngineerAuthor Commented:
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
 
rafranciscoCommented:
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
0
 
Mike MillerSoftware EngineerAuthor Commented:
All of my fields are VARCHAR
0
 
rafranciscoCommented:
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
 
Mike MillerSoftware EngineerAuthor Commented:
I'm not sure exactly what that did, but I have way less records now.
0
 
rafranciscoCommented:
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
 
Mike MillerSoftware EngineerAuthor Commented:
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
 
rafranciscoCommented:
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
 
Scott PletcherSenior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.