microsoft.public.sqlserver.programming

Roy Harvey

3/27/2007 11:22:00 AM

SELECT A.atype,
count(*) records_sent,
count(B.rno) as records_received
FROM worktab as A
LEFT OUTER
JOIN responsetab as B
ON A.rno = B.rno
WHERE number = 12
GROUP BY A.atype

Roy Harvey
Beacon Falls, CT

On Tue, 27 Mar 2007 02:50:30 -0700, Jami <jami.khan@yahoo.com> wrote:

>
>Dear All!
>
>i have following two tables and sample data
>
>create table worktab (Rno smallint,atype tinyint,number int,amount int)
>
>go
>insert into worktab values (1,1,12,6000)
>insert into worktab values (2,1,12,16000)
>insert into worktab values (3,1,12,26000)
>insert into worktab values (4,1,12,4000)
>insert into worktab values (5,3,12,4000)
>insert into worktab values (6,3,12,2000)
>insert into worktab values (7,3,12,1000)
>insert into worktab values (8,8,12,16000)
>insert into worktab values (9,8,12,6000)
>insert into worktab values (10,8,12,200)
>insert into worktab values (11,1,14,6000)
>insert into worktab values (12,3,14,300)
>insert into worktab values (13,8,14,7000)
>insert into worktab values (14,8,14,4000)
>go
>create table responseTab (Rno smallint)
>go
>insert into responseTab values(1)
>insert into responseTab values(2)
>insert into responseTab values(3)
>insert into responseTab values(5)
>insert into responseTab values(6)
>insert into responseTab values(9)
>insert into responseTab values(10)
>insert into responseTab values(11)
>insert into responseTab values(13)
>
>in worktab all the data comes and then loaded to another table from
>there
>a response comes with only rno field which shows which data loaded
>sucssesfully
>in worktab number fields shows the reference number in which data has
>been loaded
>
>i m using following queries to get the result
>
>select atype,count(*) records_sent from worktab
>where number = 12
>group by atype
>
>select atype,count(*) records_received from worktab
>where number = 12 and rno in (select rno from responsetab)
>group by atype
>
>
>but what i want is result like
>
>atype records_sent records_received
>
>in one query how it is possible
>
>
>regards,
>Jami
>
>*** Sent via Developersdex http://www.develop... ***