Home » Applications » Oracle Fusion Apps & E-Business Suite » Count and group by three columns
Count and group by three columns [message #188267] Thu, 17 August 2006 12:53
divikam123
Messages: 1
Registered: August 2006
Junior Member
The following query counts "orders" based on who created them, i.e using created_by column and counts "lines" based on who created the orders at the header level.But I also want to group this with order type.That is group by 'name' column from
"oe_transaction_types_tl.order_type_id=oe_order_headers_all.header_id" that is how they are related.

SELECT F.USER_NAME,
(SELECT COUNT(*) order_number
FROM oe_order_headers_all h,fnd_user fn
WHERE fn.user_id=h.created_by
AND fn.user_id=f.user_id
GROUP BY fn.user_name) TOTAL_ORDERS
,(SELECT COUNT(*) line_id
FROM oe_order_lines_all l,oe_order_headers_all h,fnd_user fn
WHERE h.header_id=l.header_id
AND fn.user_id=h.created_by
AND fn.user_id=f.user_id
GROUP BY fn.user_name) TOTAL_LINES
FROM
fnd_user f
WHERE f.user_name IN ('EMP2','EMP1')

Any help on this would be great.
Thanks
Divi
Previous Topic: HR Intelligence
Next Topic: GAP analysis
Goto Forum:
  


Current Time: Tue Dec 06 12:35:23 CST 2016

Total time taken to generate the page: 0.07338 seconds