February 12, 2009 at 5:59 pm
Hello All,
please help me masters....
seems to be big question, but actually it is not, i elaborated clearely
in my Accounts_Info table, it has the below fields(AcctId is primary key) & data
AcctID,DIVID,Name
--------- -------- -------
PB123, AAA, ABC Corp
PB250,AAA, G&Man
VR320,BBB, Siebel Soft
PB321,CCC ,Menta Par
VR322,CCC,Kentichy
PB267,BBB,Komma Kocha
VR123,DDD,Global Vie
PB345,DDD, GSD&K
VR560,AAA, integer
.....
.....
VR870,AAA, Corptel
DailyTrans table has below fields
Trans#,AcctID,TransDate,Amount
1,PB123,2008-11-04,-10
2,PB123,2008-11-05 ,20
3,VR320,2008-11-05,25
4,PB123,2008-11-05,15
5,VR320,2008-11-06,-20
6,PB267,2008-11-07,15
7,VR870,2008-11-07,25
8,PB267,2008-11-08,30
9,VR560,2008-11-09,35
10,VR560,2008-11-09,35
.......
SELECT a.acctID,b.divid, b.name,sum(a.Amount)
FROM DailyTrans a,accounts_Info b
WHERE a.acctID=b.acctID
and (b.DivCode = 'AAA') AND (a.DataDate between '11/1/2008' and '11/30/2008')
group by a.acctID,b.divID,b.name
order by b.name
when i run the above sql query it results as below
acctID,divid,name,sum(amount)
-------- ------ ------ ----------------
PB123,AAA,ABC Corp,25
VR870,AAA,Corptel,25
VR560,AAA,integer,70
if i issue this command
SELECT b.divid,sum(a.Amount)
FROM DailyTrans a,accounts_Info b
WHERE a.acctID=b.acctID
and (b.DivCode = 'AAA') AND (a.DataDate between '11/1/2008' and '11/30/2008')
group by b.divID
acctID,divid,name,sum(amount)
-------- ------ ------ ----------------
AAA,120
upto here everything goes fine
now the question is, when i use second query i want it be if name='integer' then it appears as a separate group
such as below
acctID,divid,name,sum(amount)
-------- ------ ------ ----------------
AAA,50
integer,70
actually integer is a account name but it needs to be separate group please....
Could any one please help me
Please Masters
Thanks in advance
a.sita
February 12, 2009 at 8:26 pm
Actually your code has put me in confusion with the names.
But just try if this works or if you get some idea
SELECT b.divid,sum(a.Amount)
FROM DailyTrans a,accounts_Info b
WHERE a.acctID=b.acctID
and (b.DivCode = 'AAA') AND (a.DataDate between '11/1/2008' and '11/30/2008')
AND (b.Name != 'integer')
group by b.divID
acctID,divid,name,sum(amount)
UNION ALL
SELECT b.name,sum(a.Amount)
FROM DailyTrans a,accounts_Info b
WHERE a.acctID=b.acctID
and (b.DivCode = 'AAA') AND (a.DataDate between '11/1/2008' and '11/30/2008')
AND (b.Name = 'integer')
group by b.name
acctID,divid,name,sum(amount)
I have put an additional AND condition in both the blocks
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 13, 2009 at 12:33 pm
Thank you very much Kings-Oldhand
its really worked as i need
thank you very much
Regards
asita
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply