SQL error is thrown when laravel pagination is used

The problem is that I have made a relationship between 3 tables and I want to list the result in the view by grouping it only by barcode.

My controller function is as follows;

public function index(Request $request){
         $data = DB::table('orderItems as SS')
            ->select(DB::raw('SS.barcode,
                MAX(SS.productName) as productName,
                SUM(SS.quantity) as quantity,
                COALESCE(NULLIF(MAX(B.salePrice),0),  MAX(B.regularPrice)) as unitPrice'))
            ->leftJoin('products as B','SS.barcode', '=', 'B.barcode')
            ->leftJoin('orders as S', 'SS.orderID', '=', 'S.ID')
            ->whereBetween('S.orderDate', ['20210616', '20210616'])
            ->orderBy('quantity', 'DESC')
            ->groupBy('SS.barcode')
            ->limit(1000)
            ->simplePaginate(5);

        return view('rapor', compact('data'));
    }

When I run the code in this way, the first page comes up, when I try to go to the next page, I get the following error;

SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server]
[SQL Server]Column 'orderItems.quantity' is invalid in the select list 
because it is not contained in either an aggregate function or the GROUP BY clause. 
(SQL: select * from (select SS.barcode, MAX(SS.productName) as productName, 
SUM(SS.quantity) as quantity, COALESCE(NULLIF(MAX(B.salePrice),0), 
MAX(B.regularPrice)) as unitPrice, 
COALESCE(NULLIF(SUM(B.salePrice * SS.quantity),0), 
row_number() over (order by [quantity] desc) as row_num from [orderItems] as [SS] 
left join [products] as [B] on [SS].[barcode] = [B].[barcode] 
left join [orders] as [S] on [SS].[orderID] = [S].[ID] where [S].[orderDate] 
between 20210616 and 20210616 group by [SS].[barcode]) as temp_table where row_num between 6 and 11 order by row_num)

If I use ->groupBy('SS.barcode','quantity') the pagination works, but it doesn’t actually give the result I want because it groups both by barcode and quantity. in this state, it shows more than one record from a product.

Example results;

->groupBy('SS.barcode') results : https://prnt.sc/16ke2sv

->groupBy('SS.barcode','quantity') results: https://prnt.sc/16kehdl

As I said, pagination works in the form of second grouping, but as you can see, the quantities are not the same.

What am I doing wrong if it’s possible for pagination to work just by grouping by barcode?

Answer

ROW_NUMBER() OVER (ORDER BY SUM(quantity) DESC) AS rownumber, it was enough to just add this line and remove the ->orderBy('quantity', 'DESC') command.

here is solution;

public function index(Request $request){
         $data = DB::table('orderItems as SS')
            ->select(DB::raw('SS.barcode,
                MAX(SS.productName) as productName,
                SUM(SS.quantity) as quantity,
                ROW_NUMBER() OVER (ORDER BY SUM(quantity) DESC) AS rownumber,
                COALESCE(NULLIF(MAX(B.salePrice),0),  MAX(B.regularPrice)) as unitPrice'))
            ->leftJoin('products as B','SS.barcode', '=', 'B.barcode')
            ->leftJoin('orders as S', 'SS.orderID', '=', 'S.ID')
            ->whereBetween('S.orderDate', ['20210616', '20210616'])
            ->groupBy('SS.barcode')
            ->limit(1000)
            ->simplePaginate(5);

        return view('rapor', compact('data'));
    }