how to add total in every rows? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

how to add total in every rows?

Author

Topic

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-02-13 : 14:59:21

is it possible to add total in every rows when using select statement?

sample data:
col1|col2
rec1 3
rec2 2
rec3 5
rec2 5

result:
col1|col2|total
rec1 3 15
rec2 2 15
rec3 5 15
rec2 5 15

mfemenel
Professor Frink

1421 Posts

Posted - 2009-02-13 : 15:23:11

are you looking for the count of occurences? If so then use this:
select col1, col2, count(*) from tablename group by col1, col2

if you're looking for the sum of some other field the this
select col1, col2, sum(someotherfield) from tablename group by col1, col2

Mike
"oh, that monkey is going to pay"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-13 : 15:24:01

SELECT col1, col2, col3 = (SELECT SUM(col2) FROM YourTable)
FROM YourTable
...

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

Subscribe to my blog

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-02-13 : 15:28:35

hi mefemenel,
I am not looking for the occurences and I can't use the sum function for it cause the data to grouped. You should take a look at my sample data. Thanks. :)

Hi tkizer,
Your suggestion would cause an error if the table is a sub query..:)
sorry :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-13 : 15:31:23

I don't understand this statement:

quote:

Your suggestion would looking for a table if your table comes in a sub query..:)

Could you rephrase?

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

Subscribe to my blog

mfemenel
Professor Frink

1421 Posts

Posted - 2009-02-13 : 15:35:03

I don't understand your sample data. What does column 3 represent?

Mike
"oh, that monkey is going to pay"

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-02-13 : 15:37:20

mfemenel

it represents the total..:)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-13 : 15:38:50

My solution provides exactly the result set you showed us. Test it out:

CREATE TABLE YourTable (col1 char(4), col2 tinyint)

INSERT INTO YourTable
SELECT 'rec1', 3 UNION ALL
SELECT 'rec2', 2 UNION ALL
SELECT 'rec3', 5 UNION ALL
SELECT 'rec2', 5

SELECT col1, col2, col3 = (SELECT SUM(col2) FROM YourTable)
FROM YourTable

DROP TABLE YourTable

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

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-13 : 15:42:19

quote:
Originally posted by k_cire0426

is it possible to add total in every rows when using select statement?

sample data:
col1|col2
rec1 3
rec2 2
rec3 5
rec2 5

result:
col1|col2|total
rec1 3 15
rec2 2 15
rec3 5 15
rec2 5 15

What is significance of having same total for all rows?

Select a.col,a.col2,b.COL3
from table a
OUTER APPLY
(Select SUM(COL2)as COL3 from TABLE )b

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-02-13 : 15:42:44

tkizer

try this..
select col1, col2, col3 = (select sum(col2) from yourtable)
from (select col1, col2, col3 from myrealtable) as yourtable

my table is a subquery i tried this but it is looking for the table "yourtable" :(

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-13 : 15:43:52

Replace with your table.

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-02-13 : 15:51:45

hi sodeep,

its significance is within my application. I have to determined if the total of the query is equal to the sum in my application.
what is the output the code you have sent that used OUTER APPLY?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-13 : 15:53:59

quote:
Originally posted by k_cire0426

hi sodeep,

its significance is within my application. I have to determined if the total of the query is equal to the sum in my application.
what is the output the code you have sent that used OUTER APPLY?

It will give you same output as Tara's Example.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-13 : 15:56:34

quote:
Originally posted by k_cire0426

tkizer

try this..
select col1, col2, col3 = (select sum(col2) from yourtable)
from (select col1, col2, col3 from myrealtable) as yourtable

my table is a subquery i tried this but it is looking for the table "yourtable" :(

You need to provide better sample data then.

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

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-13 : 15:56:57

quote:
Originally posted by k_cire0426

hi sodeep,

its significance is within my application. I have to determined if the total of the query is equal to the sum in my application.
what is the output the code you have sent that used OUTER APPLY?

Can't you try it?

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

Subscribe to my blog

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-02-13 : 16:00:39

Is the output the same if the table is a subquery?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-13 : 16:08:12

Are you referring to a derived table? What you posted earlier isn't a subquery. You need to either provide us better sample data or show us your actual query.

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

Subscribe to my blog

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-02-13 : 16:11:30

hi sodeep,
hi everyone,

It worked on sodeep example. :) I guess I need to study more on SQL functions. I did'nt there is such function like OUTER APPLY..:(

Thank you so much everyone..

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-13 : 16:14:25

quote:
Originally posted by tkizer

quote:
Originally posted by k_cire0426

hi sodeep,

its significance is within my application. I have to determined if the total of the query is equal to the sum in my application.
what is the output the code you have sent that used OUTER APPLY?

Can't you try it?

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

Subscribe to my blog

So you want all Details...



Declare @T table
(col1 char(4),col2 int)

Insert @T
Select 'rec1', 3 union all
Select 'rec2', 2 union all
Select 'rec3', 5 union all
Select 'rec2', 5


Select a.col1,a.col2,b.COL3
from @T a
OUTER APPLY
(Select SUM(COL2)as COL3 from @T )b

--output
col1   col2    col3
rec1	3	15
rec2	2	15
rec3	5	15
rec2	5	15

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-13 : 16:19:30

Another thing you should look at are CTEs (common table expressions). One advantage of CTE over a derived table is you can refer to them multiple times without having to repeat the definition.


declare @myrealTable TABLE (col1 char(4), col2 tinyint)

INSERT INTO @myrealTable
SELECT 'rec1', 3 UNION ALL
SELECT 'rec2', 2 UNION ALL
SELECT 'rec3', 5 UNION ALL
SELECT 'rec2', 5

;with yak (col1, col2)
as
(
select col1, col2 from @myrealtable
)
select col1, col2, col3 = (select sum(col2) from yak)
from yak

OUTPUT:
col1 col2 col3
---- ---- -----------
rec1 3    15
rec2 2    15
rec3 5    15
rec2 5    15

Be One with the Optimizer
TG

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-02-13 : 16:27:24

thanks again for all your help..next time i will try to clear my post. :(

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources