| 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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
