Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Not Average, not sum.. how do I get my result?! 1

Status
Not open for further replies.
MCubitt

MCubitt

Programmer
Mar 14, 2002
1,081
GB
I have the following query:

Code: 
SELECT
 ro.work_center_no Work_Centre,
 wc.description Work_Centre_Description,
 ith2.order_no Shop_Order,
 ith2.part_no Part_No,
 ip.description Part_Number_Description,
 sum(ith2.quantity) Quantity,
 to_char(avg(soo.labor_setup_time * soo.crew_size),999999.9999) Setup_Time,
 to_char(avg(soo.labor_run_factor * soo.crew_size),999999.9999) Labor_Time,
 to_char(avg(soo.labor_run_factor * soo.crew_size * ith2.quantity),999999.9999) Total_Labor_Run_Time,
 to_char(avg(soo.labor_run_factor * soo.crew_size * ith2.quantity) +  avg(soo.labor_setup_time * soo.crew_size),999999.9999) Total_Hours_Executed
FROM
 ifsapp.inventory_transaction_hist2 ith2,
 ifsapp.routing_operation ro,
 ifsapp.shop_order_operation soo,
 ifsapp.inventory_part ip,
 ifsapp.work_center wc
WHERE
 ith2.transaction_code='OOREC' and
 ith2.part_no=ro.part_no and
 ith2.part_no=ip.part_no and
 ith2.order_no = soo.order_no and
 ro.work_center_no=&work_centre and
 ro.work_center_no=wc.work_center_no and
 soo.work_center_no=ro.work_center_no and
 ith2.dated between to_date('&Start_Date','DD/MM/YYYY') and to_date('&End_Date','DD/MM/YYYY')
GROUP BY
 ro.work_center_no,
 ith2.order_no,
 ith2.part_no,
 ip.description,
 wc.description
ORDER BY
 ro.work_center_no,
 lpad(ith2.order_no,20,'0'),
 lpad(ith2.part_no,30,'0')

For one particular order, the result says qty 2.5 but it should be 5.

If I use SUM I get 15, AVG gives 2.5.

So I removed grouping and got the following (edited) result:
Code: 
Work Centre	Shop Order	Part No	Quantity	Setup Time	Labor Time	Total Labor Run Time	Total Hours Executed
127	3842	7500499	4	0	2	8	8
127	3842	7500499	4	0	2	8	8
127	3842	7500499	4	0	2	8	8
127	3842	7500499	1	0	2	2	2
127	3842	7500499	1	0	2	2	2
127	3842	7500499	1	0	2	2	2


So you can see where it gets the AVG and SUM results.

But how can I get 5?!

Thanks





There's no need for sarcastic replies, we've not all been this sad for that long!
 
Sort by date Sort by votes
How about making the grouping query from a select distinct one ?
SELECT
...,Sum(...
FROM (SELECT DISTINCT ...) AS ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
PH,

Thanks for the reply. It does *sound* right but...


Forgive my lack of knowledge, but how would that appear? Clearly not as below since I get errors!

Code: 
SELECT
 ro.work_center_no Work_Centre,
 wc.description Work_Centre_Description,
 ith2.order_no Shop_Order,
 ith2.part_no Part_No,
 ip.description Part_Number_Description,
 sum(ith2.quantity) Quantity,
 to_char(avg(soo.labor_setup_time * soo.crew_size),999999.9999) Setup_Time,
 to_char(avg(soo.labor_run_factor * soo.crew_size),999999.9999) Labor_Time,
 to_char(avg(soo.labor_run_factor * soo.crew_size * ith2.quantity),999999.9999) Total_Labor_Run_Time,
 to_char(avg(soo.labor_run_factor * soo.crew_size * ith2.quantity) +  avg(soo.labor_setup_time * soo.crew_size),999999.9999) Total_Hours_Executed
FROM
 (select distinct  ith2.order_no Shop_Order,  ith2.part_no Part_No from  ifsapp.inventory_transaction_hist2 ith2),
 ifsapp.routing_operation ro,
 ifsapp.shop_order_operation soo,
 ifsapp.inventory_part ip,
 ifsapp.work_center wc
WHERE
 ith2.transaction_code='OOREC' and
 ith2.part_no=ro.part_no and
 ith2.part_no=ip.part_no and
 ith2.order_no = soo.order_no and
 ro.work_center_no='127' and
 ro.work_center_no=wc.work_center_no and
 soo.work_center_no=ro.work_center_no and
 ith2.dated between to_date('01/07/2004','DD/MM/YYYY') and to_date('31/07/2004','DD/MM/YYYY') and 
ith2.order_no = '3842'  
GROUP BY
 ro.work_center_no,
 ith2.order_no,
 ith2.part_no,
 ip.description,
 wc.description
ORDER BY
 ro.work_center_no,
 lpad(ith2.order_no,20,'0'),
 lpad(ith2.part_no,30,'0')


thanks

There's no need for sarcastic replies, we've not all been this sad for that long!
 
Upvote 0 Downvote
Anyone willing to assist, I have a fantic user chasing me for help!

Thanks

There's no need for sarcastic replies, we've not all been this sad for that long!
 
Upvote 0 Downvote
Something like this ?
SELECT
Work_Centre,Work_Centre_Description,Shop_Order,Part_No,Part_Number_Description,
Sum(Quantity) Quantity,
to_char(Avg(Setup_Time),999999.9999) Setup_Time,
to_char(Avg(Labor_Time),999999.9999) Labor_Time,
to_char(Avg(Total_Labor_Run_Time),999999.9999) Total_Labor_Run_Time,
to_char(Avg(Total_Labor_Run_Time) + Avg(Setup_Time),999999.9999) Total_Hours_Executed
FROM (SELECT DISTINCT
ro.work_center_no Work_Centre,
wc.description Work_Centre_Description,
ith2.order_no Shop_Order,
ith2.part_no Part_No,
ip.description Part_Number_Description,
ith2.quantity Quantity,
soo.labor_setup_time * soo.crew_size Setup_Time,
soo.labor_run_factor * soo.crew_size Labor_Time,
soo.labor_run_factor * soo.crew_size * ith2.quantity Total_Labor_Run_Time,
FROM
ifsapp.inventory_transaction_hist2 ith2,
ifsapp.routing_operation ro,
ifsapp.shop_order_operation soo,
ifsapp.inventory_part ip,
ifsapp.work_center wc
WHERE
ith2.transaction_code='OOREC' and
ith2.part_no=ro.part_no and
ith2.part_no=ip.part_no and
ith2.order_no = soo.order_no and
ro.work_center_no=&work_centre and
ro.work_center_no=wc.work_center_no and
soo.work_center_no=ro.work_center_no and
ith2.dated between to_date('&Start_Date','DD/MM/YYYY') and to_date('&End_Date','DD/MM/YYYY')
) AS D
GROUP BY
Work_Centre,Work_Centre_Description,Shop_Order,Part_No,Part_Number_Description
ORDER BY
Work_Centre,
lpad(Shop_Order,20,'0'),
lpad(Part_No,30,'0')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
PH, well.. it errors at line 18:

FROM
*
ERROR at line 18:
ORA-00936: missing expression
Click to expand...

This was an extra , before the 1st FROM. I removed that and now:
) AS D
*
ERROR at line 33:
ORA-00933: SQL command not properly ended
Click to expand...


Not sure why...?


Thanks


There's no need for sarcastic replies, we've not all been this sad for that long!
 
Upvote 0 Downvote
I don't know if oracle chokes on subquery in a from clause.
You may consider create a view for the select distinct stuff and use this view in the from clause of the grouping query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
Oracle is giving you the tipoff:
) AS D
*

is a hint that it doesn't like that "AS".
Try leaving it out as follows:
Code: 
SELECT
 Work_Centre,Work_Centre_Description,Shop_Order,Part_No,Part_Number_Description,
 Sum(Quantity) Quantity,
 to_char(Avg(Setup_Time),999999.9999) Setup_Time,
 to_char(Avg(Labor_Time),999999.9999) Labor_Time,
 to_char(Avg(Total_Labor_Run_Time),999999.9999) Total_Labor_Run_Time,
 to_char(Avg(Total_Labor_Run_Time) + Avg(Setup_Time),999999.9999) Total_Hours_Executed
FROM (SELECT DISTINCT
  ro.work_center_no Work_Centre,
  wc.description Work_Centre_Description,
  ith2.order_no Shop_Order,
  ith2.part_no Part_No,
  ip.description Part_Number_Description,
  ith2.quantity Quantity,
  soo.labor_setup_time * soo.crew_size Setup_Time,
  soo.labor_run_factor * soo.crew_size Labor_Time,
  soo.labor_run_factor * soo.crew_size * ith2.quantity Total_Labor_Run_Time,
 FROM
  ifsapp.inventory_transaction_hist2 ith2,
  ifsapp.routing_operation ro,
  ifsapp.shop_order_operation soo,
  ifsapp.inventory_part ip,
  ifsapp.work_center wc
 WHERE
  ith2.transaction_code='OOREC' and
  ith2.part_no=ro.part_no and
  ith2.part_no=ip.part_no and
  ith2.order_no = soo.order_no and
  ro.work_center_no=&work_centre and
  ro.work_center_no=wc.work_center_no and
  soo.work_center_no=ro.work_center_no and
  ith2.dated between to_date('&Start_Date','DD/MM/YYYY') and to_date('&End_Date','DD/MM/YYYY')
) D
GROUP BY
Work_Centre,Work_Centre_Description,Shop_Order,Part_No,Part_Number_Description
ORDER BY
 Work_Centre,
 lpad(Shop_Order,20,'0'),
 lpad(Part_No,30,'0') ;
No guarantee that this will work, but at least the error message will go away (or change).
 
Upvote 0 Downvote
Carp,

Thanks - it works!

I'll give a star to PH who was 99.9% correct, if that's ok?


There's no need for sarcastic replies, we've not all been this sad for that long!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question
Cross Join in an Insert Into
Replies
1
Views
206
carp
carp
Tetol
  • Locked
  • Question
Update script is changing the number of records for the values updated each time I run the query
Replies
1
Views
184
carp
carp
BobThornton
  • Locked
  • Question
windows command in a sql script
Replies
1
Views
207
carp
carp
mmiah1982
  • Locked
  • Question
Extracting mutiple data items from DB with JSON string format column
Replies
2
Views
199
mmiah1982
mmiah1982
ahadali79
  • Locked
  • Question
Getting a part of string
Replies
2
Views
170
johnherman
johnherman

Part and Inventory Search

Sponsor

Back
Top