Need help with SQL Query....
Hi,
I have the following query. In Lines table I have two records with unit_price and amount. When I multiply, it displays the record wise sum and not the total sum..I need the two lines sum in total column.
SELECT rcta.trx_number "Invoice Num", hp.party_name "Customer Name",
hca.account_number, rcta.trx_date "Invoice Date",
rcta.purchase_order "PO Num",
rcta.interface_header_attribute1 "SO Num", rcta.invoice_currency_code,
rctl.line_number, msib.segment1 "Item Num", rctl.description,
rctl.uom_code, rctl.quantity_invoiced, rctl.unit_selling_price,
rctl.line_type,
(rctl.quantity_invoiced * rctl.unit_selling_price) "Line Price",
DECODE (rctl.quantity_invoiced * rctl.unit_selling_price,
NULL, rctl.extended_amount,
rctl.quantity_invoiced * rctl.unit_selling_price
) "Total Amount"
FROM apps.ra_customer_trx_all rcta,
apps.hz_parties hp,
apps.hz_cust_accounts_all hca,
apps.ra_customer_trx_lines_all rctl,
apps.mtl_system_items_b msib
WHERE rcta.trx_number IN
('13785', '13786', '13825', '13826', '13845', '13865', '13866',
'13925', '13986', '14065', '14066', '14105', '14145', '14165',
'14245', '14246', '14265', '14285', '14306', '14426', '14445',
'14446', '14548', '14570', '14571', '14626', '14645', '14646',
'14647', '14665', '14666', '14685', '14526', '14545', '14546',
'14565', '14566', '14567', '14568', '14569', '13085', '13145',
'13425', '13426', '13427', '13428', '13429', '13430', '13431',
'13432', '13433', '13435', '13436', '13437', '13665', '13686',
'13687', '13165', '13166', '13185', '13186', '13345', '13346',
'13385', '13440', '13685')
AND rcta.sold_to_customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND rctl.customer_trx_id = rcta.customer_trx_id
AND rctl.inventory_item_id = msib.inventory_item_id(+)
AND rctl.org_id = msib.organization_id(+)
AND rctl.org_id = 168
ORDER BY rcta.trx_number desc
OUTPUT:
===========
13145 ALKEG ENERGY INDUSTRY & TRADE INC. 2246 10/12/2011 00:00:00 USD 1 ATC FV Poly Fair F26R Regular Item 302084 5 143 LINE 716 716
13145 ALKEG ENERGY INDUSTRY & TRADE INC. 2246 10/12/2011 00:00:00 USD 2 Freight 1 1,150 LINE 1,150 1,150
13145 ALKEG ENERGY INDUSTRY & TRADE INC. 2246 10/12/2011 00:00:00 USD 1 5 TAX 0
13145 ALKEG ENERGY INDUSTRY & TRADE INC. 2246 10/12/2011 00:00:00 USD 1 1 TAX 0
it has line wise sum i.e 716 and 1150... I want another column with the same query to display 716+1150 in new column....both records should show 716+1150 total.
Help Apprecaited.
Thanks