Grouping Records by Month/Year and Category

In this article, we will explore how to group records in a SQL table based on two fields: date (month/year) and category. We will use the sales table as an example, with the following structure:

| id |    date    | value |  category  |

Our goal is to get the total sales value in a PHP array, grouped by month/year and category.

Understanding the Problem

We have a table with the following records:

| id | date | value | category |

| 1 | 2018-06-10 | 30.00 | cat1 |

| 2 | 2018-07-13 | 40.00 | cat3 |

| 3 | 2018-07-17 | 50.00 | cat1 |

| 4 | 2018-08-14 | 35.00 | cat3 |

| 5 | 2018-06-20 | 15.00 | cat2 |

| 6 | 2018-08-05 | 25.00 | cat2 |

| 7 | 2018-07-09 | 30.00 | cat4 |

…..

…..

We need to get the total sales value in a PHP array, grouped by month/year and category, such as:

[
  [ "2018-06", 30, 15, 0, 0 ],
  [ "2018-07", 50, 0, 40, 30 ],
  [ "2018-08", 0, 25, 35, 0 ]
]

SQL Solution

We can use the SUM aggregation function in combination with conditional statements to achieve this. We will use a CASE expression to sum the value for each category.

SELECT  
    SUM(CASE WHEN category = 'cat1' THEN value END) AS sum1,
    SUM(CASE WHEN category = 'cat2' THEN value END) AS sum2,
    SUM(CASE WHEN category = 'cat3' THEN value END) AS sum3,
    SUM(CASE WHEN category = 'cat4' THEN value END) AS sum4,
    DATE_FORMAT(date, '%Y-%m') AS date
FROM sales
GROUP BY
    DATE_FORMAT(date, '%Y-%m')
ORDER BY
    DATE_FORMAT(date, '%Y-%m');

Explanation

The basic idea behind the SUM of CASE expressions used above is that we conditionally sum the value, for each month/year, depending on what the category is. This allows us to generate separate sums for each month/year and category.

Here’s how it works:

• We use the DATE_FORMAT function to format the date in a consistent manner, so that months with different numbers of days are grouped together.
• For each group, we sum up the value using a conditional statement (CASE) based on the category. If the category matches, we include the value in the sum; otherwise, we exclude it.
• We use separate columns for each category to store their respective sums.

PHP Implementation

Once we have retrieved the data from the database, we can process it in PHP using an array:

$groupBy = [];
foreach ($rows as $row) {
    $date = $row['date'];
    if (!isset($groupBy[$date])) {
        $groupBy[$date] = [
            'cat1' => 0,
            'cat2' => 0,
            'cat3' => 0,
            'cat4' => 0
        ];
    }
    $groupBy[$date][$row['category']] += $row['value'];
}

$result = [];
foreach ($groupBy as $date => $values) {
    $result[] = [
        $date,
        $values['cat1'],
        $values['cat2'],
        $values['cat3'],
        $values['cat4']
    ];
}

Conclusion

In this article, we have explored how to group records in a SQL table based on two fields: date (month/year) and category. We used the SUM aggregation function in combination with conditional statements to achieve this. The PHP implementation involves processing the retrieved data using an array. With these techniques, you can easily extract grouped data from your database tables.

Last modified on 2023-11-16