how to put SUM in a seperate column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to put SUM in a seperate column?

Author

Topic

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-30 : 13:18:58

i have a column with many rows of values in it.. how do i select that column and make another column with just the total in it?

SELECT T1.Price, SUM(T1.Price) as 'Total'
FROM ITM1 T1
GROUP BY T1.Price

^I use this code and I get the same exact values in both columns

any help is appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 13:24:42

[code]SELECT T1.Price, T2.Total
FROM ITM1 T1
CROSS JOIN (SELECT SUM(T1.Price) as 'Total'
FROM ITM1 ) T2
[/code]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-30 : 13:25:33

You aren't grouping on the correct column. Perhaps you don't even need a grouping:

SELECT SUM(Price) as 'Total'
FROM ITM1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-30 : 13:46:24

i need to select many rows along with Total so I do need a group by, but when I execute it I get repeating rows :(

visakh your query gave me repeating rows

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-30 : 13:49:40

You need to show us sample data of what you want so that we can help write a query. It's impossible to provide an accurate solution with the information you've given us so far.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-30 : 13:54:47

SELECT T1.ItemName, T1.Price, SUM(T1.Price) as 'Total'
FROM ITM1 T1
GROUP BY T1.ItemName, T1.Price

I just want a column called Total to give me the total of the values in the Price column...but when I try to execute this both T1.Price, SUM(T1.Price) has the same values.

I need a total
thanks for all help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-30 : 13:57:14

Where's the sample data to illustrate your issue?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 13:57:21

quote:
Originally posted by DMarmolejos

i need to select many rows along with Total so I do need a group by, but when I execute it I get repeating rows :(

visakh your query gave me repeating rows

how do i select that column and make another column with just the total in it?

what do you mean by above statement? the idea i got was to retrive values from one column of table and retrieve total of that column values as a seperate column along with it which is what i've provided. You need to provide some idea about what you're expecting if this is not you want. I cant read your mind and neither can see your system to correctly determine what you want.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-30 : 13:58:47

Is this what you are looking for?

Attempting to read mind:


SELECT T1.ItemName, T1.Price, dt.PriceTotal
FROM ITM1 T1
INNER JOIN
(
	SELECT ItemName, SUM(Price) AS PriceTotal
	FORM ITM1
	GROUP BY ItemName
) dt
ON T1.ItemName = dt.ItemName

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 14:00:56

quote:
Originally posted by tkizer

Is this what you are looking for?

Attempting to read mind:

SELECT T1.ItemName, T1.Price, dt.PriceTotal
FROM ITM1 T1
INNER JOIN
(
SELECT ItemName, SUM(Price) AS PriceTotal
FORM ITM1
GROUP BY ItemName
) dt
ON T1.ItemName = dt.ItemName

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

thats the only thing we can do considering the amount of info provided by OP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 14:05:03

or is it this?

SELECT Price
FROM
(
SELECT Price
FROM ITM1 

UNION ALL

SELECT SUM(Price)
FROM ITM1  
)t

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-30 : 14:16:10

quote:
Originally posted by visakh16

or is it this?

SELECT Price
FROM
(
SELECT Price
FROM ITM1

UNION ALL

SELECT SUM(Price)
FROM ITM1
)t

this code is giving me what I want, but can u add more columns into it? (ItemName) it says it needs a group by

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-30 : 14:16:56

You can, but you need to show us sample data. I hear an echo.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 14:24:45

quote:
Originally posted by DMarmolejos

quote:
Originally posted by visakh16

or is it this?

SELECT Price
FROM
(
SELECT Price
FROM ITM1

UNION ALL

SELECT SUM(Price)
FROM ITM1
)t

this code is giving me what I want, but can u add more columns into it? (ItemName) it says it needs a group by

probably this:-

SELECT ItemName,Price
FROM
(
SELECT ItemName,Price
FROM ITM1 

UNION ALL

SELECT ItemName,SUM(Price)
FROM ITM1  
GROUP BY ItemName
)t

Also please note that this is giving total as a seperate row rather than seperate column (which is what you asked first). so in future please specify what you're looking for clearly.

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-30 : 14:47:06

that code executes great but doesnt give the total..

i can use the total on a seperate row like you had it or a column

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-30 : 14:53:29

Do you want our help?

Let's stop responding until sample data has been provided.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-30 : 15:04:30

I want this:

Total
1
2
3
6

which has been executed by this code provided by visakh:

SELECT Price
FROM
(
SELECT Price
FROM ITM1

UNION ALL

SELECT SUM(Price)
FROM ITM1
)t

BUT i also want to select ItemName in this query.. so now we need a group by

so my final desired output is:

Total ItemName
1 aaaa
2 bbbb
3 cccc
6

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-30 : 15:05:54

I thought this looked familiar. This is a duplicate thread. Here's the original: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107557

Locking this one. Continue your questions in the other thread.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources