1. Member
Join Date
Nov 1999
Posts
226
select count(orrfnbr),sum(orprtot) ,count(shipto.strfnbr) from
(
select count(orrfnbr) ,sum(orprtot) from
(
Select distinct orders.orrfnbr, orders.orprtot
FROM
ordpaymthd , orders
WHERE
ordpaymthd.omornbr = orders.orrfnbr
AND ordpaymthd.ompaymthd !='FR'
AND ordpaymthd.ompaymthd !='SW'
AND orders.orstat='C'
and trunc(shipto.sttrkdate) = '01-JAN-01'
AND orders.orrfnbr = shipto.stornbr))

I need to make this work , selecting distinct of orrfnbr ,orprtot from ordpaymthd , orders
and then calculating count and sum for the respectively and then to check out from SHIPTO
table how many items for that .

Thanks

Join Date
Jan 2001
Posts
515

## try this

select b.cnt count,b.sm sum from
(
select count(a.orrfnbr) cnt,sum(a.orprtot) sm from
(
Select distinct orders.orrfnbr, orders.orprtot
FROM
ordpaymthd , orders
WHERE
ordpaymthd.omornbr = orders.orrfnbr
AND ordpaymthd.ompaymthd !='FR'
AND ordpaymthd.ompaymthd !='SW'
AND orders.orstat='C'
and trunc(shipto.sttrkdate) = '01-JAN-01'
AND orders.orrfnbr = shipto.stornbr) a ) b

Try this. You have to select shipto.strfnbr from one of the subqueries or another table in order to do a count on it. If you add it to one of your subqueries just add the appropriate letter in front of it. You don't have to do the count and the sum in the main query because you already did it in the subquery.

3. Member
Join Date
Nov 1999
Posts
226
This is not running , I could do for 2 tables but the problem is when I add the 3rd table
SHIPTO . Please let me know what is the complete SQL as this is not running

regards

4. Member
Join Date
Nov 1999
Posts
226
Thanks done

regards

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•