sum total - SQL Server Forums

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

sum total

Author

Topic

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-03-13 : 08:14:47

[code]
declare @a1 table (
id int not null identity(1,1),
name nchar(20),
dollar int,
rubl int)
insert into @a1
(name,dollar,rubl)
values
('kelly',1,1)
insert into @a1
(name,dollar,rubl)
values
('kelly',2,1)
insert into @a1
(name,dollar,rubl)
values
('jonny',2,1)
insert into @a1
(name,dollar,rubl)
values
('jonny',2,1)

select name,
sum(dollar) as dollar,
sum(rubl) as rubl
from @a1 group by name

name dollar rubl
-------------------- ----------- -----------
jonny 4 2
kelly 3 2

how i make get result table

name dollar rubl dollar+rubl
-------------------- ----------- -----------
jonny 4 2 6
kelly 3 2 5
total 7 4 11
[/code]

http://sql-az.tr.gg/

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-13 : 08:21:16

You should/must handle such cases in application/layer. This is not something that should be catered at DB level since its a simply computation

by the way ..

SELECT A.Name,Dollar,Rubl,Total
FROM (
select name,
sum(dollar) as dollar,
sum(rubl) as rubl
from @a1 group by name
UNION
SELECT 'Total'
,sum(dollar) as dollar
,sum(rubl) as rubl
FROM @a1 )A
INNER JOIN (
SELECT Name, sum(dollar) + sum(rubl) Total
FROM @a1
Group by Name
UNION
SELECT 'Total', sum(dollar) + sum(rubl) Total
FROM @a1 ) B on A.Name=B.Name

Cheers
MIK

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-03-13 : 08:37:20

thank you very much

http://sql-az.tr.gg/

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-03-13 : 08:44:00

i have one guery

how i make i
result table with title

THANKS ALL SQL.TEAM

name dollar rubl dollar+rubl
-------------------- ----------- -----------
jonny 4 2 6
kelly 3 2 5
total 7 4 11

http://sql-az.tr.gg/

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-13 : 08:57:29

Sounds that you're still interested in the DB approach .. once again not a correct one

SELECT A.Name AS name ,Dollar as dollar,Rubl as rubl ,Total as [dollar+rubl]
...
...
...

Cheers
MIK

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2014-03-13 : 09:08:48

thanks i wrote this

create proc test_make
as
declare @a1 table (
id int not null identity(1,1),
name nchar(20),
dollar int,
rubl int)
insert into @a1
(name,dollar,rubl)
values
('kelly',1,1)
insert into @a1
(name,dollar,rubl)
values
('kelly',2,1)
insert into @a1
(name,dollar,rubl)
values
('jonny',2,1)
insert into @a1
(name,dollar,rubl)
values
('jonny',2,1)
SELECT A.Name,Dollar,Rubl,Total
FROM (
select name,
sum(dollar) as dollar,
sum(rubl) as rubl
from @a1 group by name
UNION
SELECT 'Total'
,sum(dollar) as dollar
,sum(rubl) as rubl
FROM @a1 )A
INNER JOIN (
SELECT Name, sum(dollar) + sum(rubl) Total
FROM @a1
Group by Name
UNION
SELECT 'Total', sum(dollar) + sum(rubl) Total
FROM @a1 ) B on A.Name=B.Name

EXECUTE sp_makewebtask @outputfile = 'd:\Test_1.xls',
@query = 'exec test_make',
@resultstitle=N'????????? ?????????',
@HTMLHeader=2,
@lastupdated=0

http://sql-az.tr.gg/

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-03-21 : 02:13:21

There is another way to get it without using JOIN'S Concept

Run the below code.......Most like MIC but i never used JOIN'S

SELECT Name ,SUM(Dollar) AS Dollar,Sum(rubl) AS Rubl,SUM(Dollar+Rubl) AS [Dollar+Rubl] FROM @a1 GROUP BY Name
UNION
SELECT 'Total',SUM(Dollar) As Dollar,SUM(rubl) AS Rubl,SUM(Dollar+Rubl) AS [Dollar+Rubl] From @a1

Murali Krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources