Home » SQL & PL/SQL » SQL & PL/SQL » query help (oracle 10g)
query help [message #420055] Wed, 26 August 2009 14:43 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi
I have the following query which works fine:
select sum(t1.AMOUNT) po_paymnts,t1.PO_NO,t2.PS_QTY,t2.AMOUNT encumb_amnt,t2.ORIGINAL_AMOUNT,(t2.AMOUNT)/(t2.PS_QTY) unit_cost,(sum(t1.AMOUNT))/((t2.AMOUNT)/(t2.PS_QTY)) ytd_qty_purchased_tons,(t2.AMOUNT-sum(t1.AMOUNT)) balance_amnt
from dw_ps_all_payments_fact t1,dw_ps_ap_po_rq_fact t2
where t1.PO_NO=t2.PS_NO
and t2.OBJECT='545000'
group by t1.PO_NO,t2.AMOUNT,t2.ORIGINAL_AMOUNT,t2.PS_QTY

But when i try to insert a date field "Invoice_dt" I'm getting multiple rows for each PO_NO (which makes sense for each date), What i want to accomplish is to summarise these dates for each fiscal year, so that i get data summarised to each fiscal year.Any help is greatly appreciated.

Thanks

[fixed code tags]

[Updated on: Thu, 27 August 2009 04:25] by Moderator

Report message to a moderator

Re: query help [message #420056 is a reply to message #420055] Wed, 26 August 2009 14:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: query help [message #420057 is a reply to message #420056] Wed, 26 August 2009 14:54 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Sorry about the earlier post:

SELECT Sum(t1.amount) po_paymnts,
t1.po_no,
t2.ps_qty,
t2.amount encumb_amnt,
t2.original_amount,
(t2.amount) / (t2.ps_qty) unit_cost,
(Sum(t1.amount)) / ((t2.amount) / (t2.ps_qty)) ytd_qty_purchased_tons,
(t2.amount - Sum(t1.amount)) balance_amnt
FROM dw_ps_all_payments_fact t1,
dw_ps_ap_po_rq_fact t2
WHERE t1.po_no = t2.ps_no
AND t2.OBJECT = '545000'
GROUP BY t1.po_no,
t2.amount,
t2.original_amount,
t2.ps_qty

Thanks
Re: query help [message #420058 is a reply to message #420055] Wed, 26 August 2009 14:57 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Sorry about the earlier post:

so much better

SELECT   Sum(t1.amount)                                 po_paymnts, 
         t1.po_no, 
         t2.ps_qty, 
         t2.amount                                      encumb_amnt, 
         t2.original_amount, 
         (t2.amount) / (t2.ps_qty)                      unit_cost, 
         (Sum(t1.amount)) / ((t2.amount) / (t2.ps_qty)) ytd_qty_purchased_tons, 
         (t2.amount - Sum(t1.amount))                   balance_amnt 
FROM     dw_ps_all_payments_fact t1, 
         dw_ps_ap_po_rq_fact t2 
WHERE    t1.po_no = t2.ps_no 
         AND t2.OBJECT = '545000' 
GROUP BY t1.po_no, 
         t2.amount, 
         t2.original_amount, 
         t2.ps_qty


>But when i try to insert a date field "Invoice_dt"
INSERT? Invoice_dt?

>I'm getting multiple rows for each PO_NO (which makes sense for each date),
I don't see multiple rows

>What i want to accomplish is to summarise these dates for each fiscal year,
Again what dates?

>so that i get data summarised to each fiscal year.Any help is greatly appreciated.

You offer us no real clues, so you get no real advice.

[Updated on: Wed, 26 August 2009 15:03]

Report message to a moderator

Re: query help [message #420060 is a reply to message #420058] Wed, 26 August 2009 15:09 Go to previous messageGo to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Can you please tell me how to post my results as a table, should i copy paste?
BlackSwan wrote on Wed, 26 August 2009 14:57
>Sorry about the earlier post:

so much better

SELECT   Sum(t1.amount)                                 po_paymnts, 
         t1.po_no, 
         t2.ps_qty, 
         t2.amount                                      encumb_amnt, 
         t2.original_amount, 
         (t2.amount) / (t2.ps_qty)                      unit_cost, 
         (Sum(t1.amount)) / ((t2.amount) / (t2.ps_qty)) ytd_qty_purchased_tons, 
         (t2.amount - Sum(t1.amount))                   balance_amnt 
FROM     dw_ps_all_payments_fact t1, 
         dw_ps_ap_po_rq_fact t2 
WHERE    t1.po_no = t2.ps_no 
         AND t2.OBJECT = '545000' 
GROUP BY t1.po_no, 
         t2.amount, 
         t2.original_amount, 
         t2.ps_qty


>But when i try to insert a date field "Invoice_dt"
INSERT? Invoice_dt?

>I'm getting multiple rows for each PO_NO (which makes sense for each date),
I don't see multiple rows

>What i want to accomplish is to summarise these dates for each fiscal year,
Again what dates?

>so that i get data summarised to each fiscal year.Any help is greatly appreciated.

You offer us no real clues, so you get no real advice.

Re: query help [message #420061 is a reply to message #420055] Wed, 26 August 2009 15:22 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Can you please tell me how to post my results as a table, should i copy paste?
Look familiar?
http://www.orafaq.com/forum/m/420060/136107/#msg_420056
Re: query help [message #420147 is a reply to message #420060] Thu, 27 August 2009 04:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming that you're just trying to add the Year part of the invoice date as one of the columns that you're grouping by, try this:

SELECT   Sum(t1.amount)                                 po_paymnts, 
         t1.po_no, 
         t2.ps_qty, 
         t2.amount                                      encumb_amnt, 
         t2.original_amount, 
         (t2.amount) / (t2.ps_qty)                      unit_cost, 
         (Sum(t1.amount)) / ((t2.amount) / (t2.ps_qty)) ytd_qty_purchased_tons, 
         (t2.amount - Sum(t1.amount))                   balance_amnt,
         to_char(invoice_dt,'yyyy') inv_year
FROM     dw_ps_all_payments_fact t1, 
         dw_ps_ap_po_rq_fact t2 
WHERE    t1.po_no = t2.ps_no 
         AND t2.OBJECT = '545000' 
GROUP BY t1.po_no, 
         t2.amount, 
         t2.original_amount, 
         t2.ps_qty,
         to_char(invoice_dt,'yyyy')
Re: query help [message #420187 is a reply to message #420147] Thu, 27 August 2009 08:03 Go to previous message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Thank You very much
Previous Topic: trigger
Next Topic: drop a partitioned table oracle11g
Goto Forum:
  


Current Time: Sun Dec 04 22:57:44 CST 2016

Total time taken to generate the page: 0.04086 seconds