create a sum field but still see detail

[sonny1974]

i want to see the detail but i want another field that will qty_ord and qty_rcv for each PO

so the total for PO 100001 is 6 ordered, 3 received
100002 40 order and 40 received

how would i put two more field that would give me the totals for ordered and received but also see the detail data

select
pod.po_nbr,pod.qty_ord,pod.qty_rcv,unit_prc

from pod_table pod
left join po_table po on pod.po_nbr = po.po_nbr
left join vm_table vm on pod.vendor = vm.vendor
order by pod.po_nbr, pod.po_item_nbr




PO_NBR QTY_ORD QTY_RCV UNIT_PRC
100001 2 2 1225.79
100001 1 1 1225.79
100001 2 0 1810.96
100001 1 0 1810.96
100002 10 10 0.627
100002 10 10 0.899
100002 10 10 0.396
100002 10 10 0.676

 

[Dagon]

You need to look at windowing functions. These have the format:

select a, b, c, sum(d) over (partition by a, b)
from table

 

[sonny1974]

great, it worked

thanks dagon

 

[sonny1974]

can i do this?

if the sum_ord = sum_rcv then a Y if the dont then N


PO_NBR QTY_ORD QTY_RCV UNIT_PRC SUM_ORD SUM_RCV
100001 2 2 1225.79 6 3
100001 1 1 1225.79 6 3
100001 2 0 1810.96 6 3
100001 1 0 1810.96 6 3
100002 10 10 0.627 40 40
100002 10 10 0.899 40 40
100002 10 10 0.396 40 40
100002 10 10 0.676 40 40

 

[SantaMufasa]

Sonny,

It appears that you are using SQL*Plus. In that environment, you can use this code:

break on po_nbr skip 1 on report
compute sum of qty_ord qty_rcv on po_nbr report
select * from pod_table order by po_nbr;

    PO_NBR    QTY_ORD    QTY_RCV   UNIT_PRC
---------- ---------- ---------- ----------
    100001          2          2    1225.79
                    1          1    1225.79
                    2          0    1810.96
                    1          0    1810.96
********** ---------- ----------
sum                 6          3

    100002         10         10       .627
                   10         10       .899
                   10         10       .396
                   10         10       .676
********** ---------- ----------
sum                40         40

           ---------- ----------
sum                46         43

Notice that the code sub-totals at po_nbr breaks and grand-totals at the end of the report.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Dagon]

You can probably do that by adding a case (or decode) statement to your SQL.

 

[sonny1974]

thanks i got it to work

 

[SantaMufasa]

Sonny,

If I understand your most recent question, then you can use this code (in addition to my earlier code):

col flag format a4
select pod.*
      ,decode(sign(qty_ord-qty_rcv),-1,'N',0,'Y',1,'N') flag
  from pod_table pod
 order by po_nbr;

    PO_NBR    QTY_ORD    QTY_RCV   UNIT_PRC FLAG
---------- ---------- ---------- ---------- ----
    100001          2          2    1225.79 Y
                    1          1    1225.79 Y
                    2          0    1810.96 N
                    1          0    1810.96 N
********** ---------- ----------
sum                 6          3

    100002         10         10       .627 Y
                   10         10       .899 Y
                   10         10       .396 Y
                   10         10       .676 Y
********** ---------- ----------
sum                40         40

           ---------- ----------
sum                46         43

Let us know if this resolves your question.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Geez...I'm always a minute or two behind the other posts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

...But at least we are not like some of the other database-vendor forums where it may be days before one sees a reply to their post.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]