Pull data summing up the amount column such that the result is grouped by customers, month and year-CodePudding

I have a customers table and a customer transactions table.

This is the customers' table below.

This is the customer transactions table below.

This is what I want to achieve below:

I want to be able to sum up the amount column such that the result is grouped by customers and month and year.

This is what I've done so far:

I created a model for both Customers and Customers transactions. I have this relationship in the Customer transactions model:

    public function customer()
    {
        return $this->belongsTo(Customer::class);
    }

I also have a Customer controller where I have the logic to fetch the data. In my CustomerController file:

    public function index()
    {
        $transactions = CustomerTransaction::with('customer')->get();

        dd($transactions);
    }

CodePudding user response：

I was able to resolve it. In the CustomerController file, this is what I now have in the index method:

$transactions = CustomerTransaction::with('customer')
->selectRaw('customer_id, sum(amount) as amount, MONTH(date_created) as month, YEAR(date_created) as year')
->groupBy('customer_id', 'month', 'year')
->get();

Then in my view, I have this:

                    <table >
                        <tr>
                            <th>S/N</th>
                            <th>Name</th>
                            <th>Amount</th>
                            <th>Year month</th>
                        </tr>
                        @foreach ($transactions as $transaction)
                            <tr>
                                <td>{{ $loop->iteration }}</td>
                                <td>{{ $transaction->customers->name }}</td>
                                <td>{{ $transaction->amount }}</td>
                                <td>{{ $transaction->year }}-{{ sprintf('d', $transaction->month) }}</td>
                            </tr>
                        @endforeach
                    </table>

CodePudding user response：

I was able to write the SQL query:

select ROW_NUMBER() OVER(ORDER BY (select null)) as id,
name as `Customer name`,
sum(amount) as `Total amount`,
concat(YEAR(date_created), '-',
lpad(MONTH(date_created), 2, '0')) as `Year month`
from customer_transactions
join customers on customers.id=customer_transactions.customer_id
group by `customer_id`, `Year month`
order by `customer_id`, `Year month`