SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sum total
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 03/13/2014 :  08:14:47  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote

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


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

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/13/2014 :  08:21:16  Show Profile  Reply with Quote
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
Go to Top of Page

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 03/13/2014 :  08:37:20  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
thank you very much

http://sql-az.tr.gg/
Go to Top of Page

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 03/13/2014 :  08:44:00  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
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/
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/13/2014 :  08:57:29  Show Profile  Reply with Quote
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
Go to Top of Page

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 03/13/2014 :  09:08:48  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
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/
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

India
110 Posts

Posted - 03/21/2014 :  02:13:21  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000