[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Total of columns

Posted on 2005-04-21
13
Medium Priority
?
227 Views
Last Modified: 2010-03-19
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
Comment
Question by:Mike Miller
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 5

Expert Comment

by:obahat
ID: 13835253
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 51

Expert Comment

by:Steve Bink
ID: 13835257
SELECT Sum([MyColumn]) FROM MyTable
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13835276
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:Mike Miller
ID: 13835394
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

by:rafrancisco
ID: 13835505
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
 

Author Comment

by:Mike Miller
ID: 13835522
All of my fields are VARCHAR
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13835554
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

by:Mike Miller
ID: 13836140
I'm not sure exactly what that did, but I have way less records now.
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13836190
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 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 13836993
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

by:Mike Miller
ID: 13837066
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

by:rafrancisco
ID: 13837090
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 70

Expert Comment

by:Scott Pletcher
ID: 13837811
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

872 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