'fetch multiple records from a single table having same id

I am writing a function in laravel 5.8 in which I want to get sum of the user amount that lies within my selected range, I am using the two input fields in view which they get the price range "T0 and From" e.g if the user enters 100 and 1000, it should show the list of the users who has amount > 100 and amount < 1000. The problem is there are multiple records of the same users as shown below in the table. I want to sum the amount of the users and show the user if their amount is within range

table name= deposits

<table>
<thead>
<tr>
<th>id</th>
<th>user_id</th>
<th>name</th>
<th>amount</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>12</td>
<td>ali</td>
<td>100</td>
</tr>
<tr>
<td>2</td>
<td>12</td>
<td>ali</td>
<td>800</td>
</tr>
<tr>
<td>3</td>
<td>12</td>
<td>ali</td>
<td>50</td>
</tr>
<tr>
<td>4</td>
<td>15</td>
<td>khan</td>
<td>1100</td>
</tr>
<tr>
<td>6</td>
<td>9</td>
<td>james</td>
<td>850</td>
</tr>
<tr>
<td>7</td>
<td>9</td>
<td>james</td>
<td>90</td>
</tr>
</tbody>
</table>
laraveleloquent


Solution 1:[1]

This behavior of your query is correct, to get what you need it is necessary to work on the query. Assuming this fields in your deposits table: id, user_id, name, amount in according with data showing in the table.

You need to sum the amount grouping by user_id

Example query:

$deposit = Deposits::select(
                       'id',
                       'user_id',
                       'name',
                       'sum(amount)',
                    )->whereBetween('amount', [100, 1000])
                    ->groupBy('user_id')->get();

Solution 2:[2]

DB::table('deposits')
        ->selectRaw('user_id, SUM(amount) as total_amount')
        ->where('amount', '>', '100')
        ->where('amount', '<', '1000')
        ->groupBy('user_id')
        ->get();

Result is:

[
  {
    "user_id":9,
    "total_amount":850
  },
  {
    "user_id":12,
    "total_amount":800
  }
]

Solution 3:[3]

app('db')
   ->table('users')
   ->select('name')
   ->selectRaw("SUM(amount) as total_amounts")
   ->havingBetween('total_amounts', [ 100, 1000 ])
   ->groupBy('user_id')
   ->get();

You can use the raw query for the sum per user and use the having between for the sum of amounts.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 64Bit1990
Solution 2 Dmitry Fedorov
Solution 3

Related Questions