Updated June 2026. Tested on Laravel 13, PHP 8.4 and Vue 3.

I built this for an ERP system that runs in a few supermarkets. A client with several branches wanted to see which product categories sell the most, and in which months, so he could stock up before the busy periods. That is a classic report: product categories down the side, months across the top, and the number of sales in each cell. This post walks through the hard part, turning rows of orders into that month by month grid, and then handing it to a chart.

The tables involved are the usual sales ones: categories, products, orders and order_details. We add one helper table to make the months line up.

Why a calendar table

The report needs a column for every month in the selected range, even months where a category had no sales. If we group straight off the order dates, a category with no orders in February simply has no February column, and the grid breaks. So we keep a small monthly_calendar table listing every month, and build the columns from it. That guarantees a cell for every month, filled with zero where there were no sales.

CREATE TABLE monthly_calendar (
    month_label varchar(20) NOT NULL,   -- e.g. January-2026
    month_start date NOT NULL
);

Seed it with every month your business has been running. A simple scheduled job can add next year's twelve rows each January, so you never run out.

Step 1: sales per category per month

First, group the orders by category and month.

SELECT
    c.id   AS category_id,
    c.name AS category_name,
    DATE_FORMAT(o.order_date, '%M-%Y') AS month_label,
    SUM(od.quantity) AS total_sales
FROM order_details od
JOIN orders     o ON od.order_id = o.id
JOIN products   p ON od.product_id = p.id
JOIN categories c ON p.category_id = c.id
WHERE o.order_date BETWEEN ? AND ?
GROUP BY c.id, month_label
ORDER BY o.order_date;

This gives one row per category per month that had sales. The gap is the months with no sales, which is what the calendar table fixes next.

Step 2: pivot months into columns

We want each month as its own column. MySQL has no built in pivot, so the standard trick is to build a CASE expression per month and let an aggregate roll them up. Because the months are dynamic, we generate that list of CASE expressions from the calendar table.

SET @cols = NULL;

SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
        'MAX(CASE WHEN grid.month_label = ''', month_label,
        ''' THEN COALESCE(s.total_sales, 0) END) AS `', month_label, '`'
    )
) INTO @cols
FROM monthly_calendar
WHERE month_start BETWEEN ? AND ?;

COALESCE(..., 0) is what turns a missing month into a zero rather than a blank.

Step 3: wrap it in a stored procedure

The final query cross joins categories with the calendar to get every category and month pair, then left joins the sales from step one. Since the column list is dynamic, we build the statement as a string and run it with PREPARE. Keeping it in a stored procedure means the app just calls it with two dates.

DELIMITER $$
CREATE PROCEDURE sales_by_category_month(IN start_date DATE, IN end_date DATE)
BEGIN
    -- step 1 into a temp table
    DROP TEMPORARY TABLE IF EXISTS tmp_sales;
    CREATE TEMPORARY TABLE tmp_sales AS
        SELECT c.id AS category_id, c.name AS category_name,
               DATE_FORMAT(o.order_date, '%M-%Y') AS month_label,
               SUM(od.quantity) AS total_sales
        FROM order_details od
        JOIN orders o     ON od.order_id = o.id
        JOIN products p   ON od.product_id = p.id
        JOIN categories c ON p.category_id = c.id
        WHERE o.order_date BETWEEN start_date AND end_date
        GROUP BY c.id, month_label;

    -- step 2: build the dynamic columns
    SET @@group_concat_max_len = 75000;
    SET @cols = NULL;
    SELECT GROUP_CONCAT(DISTINCT
        CONCAT('MAX(CASE WHEN grid.month_label = ''', month_label,
               ''' THEN COALESCE(s.total_sales, 0) END) AS `', month_label, '`')
    ) INTO @cols
    FROM monthly_calendar
    WHERE month_start BETWEEN start_date AND end_date;

    -- step 3: assemble and run
    SET @sql = CONCAT(
        'SELECT grid.category_id, grid.category_name, ', @cols, '
         FROM (
            SELECT c.id AS category_id, c.name AS category_name,
                   cal.month_label, cal.month_start
            FROM monthly_calendar cal CROSS JOIN categories c
            WHERE cal.month_start BETWEEN ''', start_date, ''' AND ''', end_date, '''
         ) grid
         LEFT JOIN tmp_sales s
            ON grid.category_id = s.category_id
           AND grid.month_label = s.month_label
         GROUP BY grid.category_id'
    );

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    DROP TEMPORARY TABLE tmp_sales;
END $$
DELIMITER ;

Step 4: call it from Laravel

Calling the procedure from Laravel is short and clean. Use DB::select with bound parameters. There is no need for raw PDO handling.

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Carbon;

public function salesChart(Request $request)
{
    $start = $request->date('start_date', Carbon::now()->startOfYear());
    $end   = $request->date('end_date', Carbon::now());

    $rows = DB::select('CALL sales_by_category_month(?, ?)', [
        $start->toDateString(),
        $end->toDateString(),
    ]);

    $months = DB::table('monthly_calendar')
        ->whereBetween('month_start', [$start, $end])
        ->orderBy('month_start')
        ->pluck('month_label');

    $series = collect($rows)->map(function ($row) {
        $values = (array) $row;
        unset($values['category_id'], $values['category_name']);

        return [
            'name' => $row->category_name,
            'data' => array_values($values),
        ];
    });

    return response()->json([
        'labels' => $months,
        'series' => $series,
    ]);
}

The shape is deliberately generic: a list of month labels and a series per category, each with its data array. That feeds straight into any chart library.

Step 5: draw it in Vue

On the front end, fetch that JSON and pass it to a chart. Any modern library works, Chart.js and ApexCharts are both good free choices. The idea is the same: the labels become the x axis, and each category in series becomes a bar or line.

<script setup>
import { ref, onMounted } from 'vue'
import axios from 'axios'

const labels = ref([])
const series = ref([])

onMounted(async () => {
  const { data } = await axios.get('/api/sales-chart')
  labels.value = data.labels
  series.value = data.series
})
</script>

Hand labels and series to your chart component and you have a month by month sales chart per category, with the empty months showing as zero instead of leaving holes.

The takeaway is that the work lives in the SQL. Once you pivot the months into columns and fill the gaps from a calendar table, the Laravel and Vue parts are just passing a tidy JSON shape along. Questions welcome in the comments.