-
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
-
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.
-
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
-