Laravel 5.1 Average

Question

I have an SQL query which should return the Average cost price from the table.

I have a few joins because I need to pull certain statuses from the order header.

See eloquent query.

return $avgsell = DB::table('STK_STOCK')
        ->join('ORD_DETAIL','ORD_DETAIL.OD_ORDER_NUMBER','=','ORD_HEADER.OH_ORDER_NUMBER')
        ->where('ORD_HEADER','ORD_DETAIL.OD_STOCK_CODE','=',$code)->where('ORD_HEADER.OH_PRIORITY','<>',4)->where('ORD_DETAIL.OD_QTYORD','<>','0')
        ->avg('ORD_DETAIL.OD_UNITCST');

I get the following error

SQLSTATE[42000]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near 'ORD_HEADER'. (SQL: select avg([ORD_DETAIL].[OD_UNITCST]) as aggregate from [STK_STOCK] inner join [ORD_DETAIL] on [ORD_DETAIL].[OD_ORDER_NUMBER] = [ORD_HEADER].[OH_ORDER_NUMBER] where 0005-20-02-01 [ORD_HEADER] = ORD_DETAIL.OD_STOCK_CODE [ORD_HEADER].[OH_PRIORITY] <> 4 and [ORD_DETAIL].[OD_QTYORD] <> 0)

score 1 · Answer 1 · answered Apr 28 '16 at 10:12

1

In where condition the error coming because you are passing three params

->where('ORD_HEADER','ORD_DETAIL.OD_STOCK_CODE','=',$code)

remove ORD_HEADER or ORD_DETAIL.OD_STOCK_CODE

the final code

 return $avgsell = DB::table('STK_STOCK')
        ->join('ORD_DETAIL','ORD_DETAIL.OD_ORDER_NUMBER','=','ORD_HEADER.OH_ORDER_NUMBER')
        ->where('ORD_DETAIL.OD_STOCK_CODE','=',$code)->where('ORD_HEADER.OH_PRIORITY','<>',4)->where('ORD_DETAIL.OD_QTYORD','<>','0')
        ->avg('ORD_DETAIL.OD_UNITCST');

answered Apr 28 '16 at 10:12

Veerendra Borra

1,286
14
24

Thanks Veerendra - I have amended per your example but I got the following error: The multi-part identifier "ORD_HEADER.OH_ORDER_NUMBER" could not be bound. – Dev.W Apr 28 '16 at 13:01
Are you joining three tables? i think your query is not correct.paste your models here or check below link [http://stackoverflow.com/questions/7314134/the-multi-part-identifier-could-not-be-bound] – Veerendra Borra Apr 29 '16 at 04:33
example query: return $avgsell = DB::table('users') ->join('posts','users.id','=','posts.users_id') ->where('posts.name','=',$name)->get(); – Veerendra Borra Apr 29 '16 at 04:36

Laravel 5.1 Average

1 Answers1