Good day, I would like to ask if its possible to query in one all the data by month in a year.
desired data to display
+---------+---------+------------------------+
| Join Table |
+---------+---------+---+---+-----+----------+
| acc_no | acc_name|Jan|Feb|March| up to dec|
+---------+---------+---+---+-----+----------+
| 6220 | Sales |
| 6221 | Material|to be field with sum,
| 6222 | Others |group by acc_no,year,month
| | |
+---------+---------+------------------------+
so far what i have created is only for 1 month
+-----------+--------------+
| Join Table |
+---------+---------+------+
| acc_no | acc_name|Jan |
+---------+---------+------+
| 6220 | Sales |sum |
| 6221 | Material|data |
| 6222 | Others | |
| | | |
+---------+---------+------+
Controller
$year=2024;//temporary fixed var,my plan is on select option afterwards
$january=1;//temporary fixed var,my plan is on select option afterwards
$january_Balance= DB::table('general_ledgers')
->select('general_ledgers.acc_no', 'account_codes.account_name','general_ledgers.jnl_date',
'general_ledgers.name_supplier_desc as desciption','general_ledgers.currency','general_ledgers.amount',
'general_ledgers.fx_rate','general_ledgers.d_rate'
)
->selectRaw("SUM(general_ledgers.amount) as total_amount")
->selectRaw("YEAR(general_ledgers.jnl_date) as year,MONTH(general_ledgers.jnl_date) as month")
->groupBy('account_codes.acc_no','year','month')
->whereYear('general_ledgers.jnl_date', $year)
->whereMonth('general_ledgers.jnl_date', $january)
->leftJoin('account_codes', 'account_codes.acc_no', '=', 'general_ledgers.acc_no')
->get();
return Inertia::render('Accounting/TrialBalance',[
'items' =>$january_Balance
]);
Vue page
<table class="table table-zebra ">
<!-- head -->
<thead>
<tr>
<th>Acc No.</th>
<th>Account Name</th>
<th>January</th>
<th>February</th>
<th>March</th>
<th>April</th>
<th>May</th>
<th>June</th>
<th>July</th>
<th>August</th>
<th>September</th>
<th>October</th>
<th>November</th>
<th>December</th>
</tr>
</thead>
<tbody>
<tr v-for="item in items" :key="item.id" >
<td>{{ item.acc_no }}</td>
<td>{{ item.account_name }}</td>
<td>{{ item.total_amount }}</td>
<td>for other months</td>
</tr>
</tbody>
</table>
so if possible i would like to do it on one query,the only thing i can think of is making multiple of the query per month but i would like to do it if possible in one query instead of making redundant query over and over per month
edit Actual Data or tables
+-----------+--------------+
| account codes |
+---------+---------+------+
| acc_no | acc_name|Desc |
+---------+---------+------+
| 6220 | Sales |some |
| 6221 | Material|remark|
| 6222 | Others | |
| | | |
+---------+---------+------+
+-----------+--------------+---------+--------+
| general_ledgers | |
+---------+---------+------+---------+--------+
| id | acc_id|Desc |currency|amount+jnl_date|
+---------+---------+----------------+--------+
| 1 | 6220 |some |JYP |30000 |01/27/24|
| 2 | 6221 |remark|PHP |-20000|02/2/24 |
| 3 | 6223 | |JYP |15000 |03/5/24 |
+---------+---------+----------------+--------+
Although you did not mention your table structure with data, I have taken some references from the code you mentioned. I am providing you with a raw query:
SELECT
general_ledgers.acc_no,
account_codes.account_name,
general_ledgers.jnl_date,
general_ledgers.name_supplier_desc AS desciption,
general_ledgers.currency,
general_ledgers.amount,
general_ledgers.fx_rate,
general_ledgers.d_rate,
SUM(CASE WHEN YEAR(general_ledgers.jnl_date)=2024 AND MONTH(general_ledgers.jnl_date) =1 THEN general_ledgers.amount ELSE 0 END) AS january,
SUM(CASE WHEN YEAR(general_ledgers.jnl_date)=2024 AND MONTH(general_ledgers.jnl_date) =2 THEN general_ledgers.amount ELSE 0 END) AS february,
/*other months*/
SUM(CASE WHEN YEAR(general_ledgers.jnl_date)=2024 AND MONTH(general_ledgers.jnl_date) =12 THEN general_ledgers.amount ELSE 0 END) AS december
FROM
general_ledgers
LEFT JOIN
account_codes ON account_codes.acc_no = general_ledgers.acc_no
WHERE general_ledgers.jnl_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY
general_ledgers.acc_no;
Or since we'll be fetching data for year 2024 only, you can also remove the YEAR() part and use:
SELECT
general_ledgers.acc_no,
account_codes.account_name,
general_ledgers.jnl_date,
general_ledgers.name_supplier_desc AS desciption,
general_ledgers.currency,
general_ledgers.amount,
general_ledgers.fx_rate,
general_ledgers.d_rate,
SUM(CASE WHEN MONTH(general_ledgers.jnl_date) =1 THEN general_ledgers.amount ELSE 0 END) AS january,
SUM(CASE WHEN MONTH(general_ledgers.jnl_date) =2 THEN general_ledgers.amount ELSE 0 END) AS february,
/*other months*/
SUM(CASE WHEN MONTH(general_ledgers.jnl_date) =12 THEN general_ledgers.amount ELSE 0 END) AS december
FROM
general_ledgers
LEFT JOIN
account_codes ON account_codes.acc_no = general_ledgers.acc_no
WHERE general_ledgers.jnl_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY
general_ledgers.acc_no;
You can do something like this and get results for an entire 12 months in 1 query. You can work out the rest on your own.