DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: SQL HELP

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

  2. #2
    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. #3
    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. #4
    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
  •  


Click Here to Expand Forum to Full Width