Count entries grouped by id and month from denormalized database

Question

I have a table (tbl_operations) with rows of where the id column values may be comma-delimited. I want to get the count of each OpId for each month. I am trying to accomplish this through pure sql, but without success.

from this view

OpId	OpDate
3	2022-01-03
5,3	2022-01-15
4	2022-01-27
5	2022-02-01
7	2022-02-09
3,2	2022-01-16

to this

OpId	count	Month
2	1	01
3	3	01
4	1	01
5	1	01
5	1	02
7	1	02

I am stuck here. Can someone enlighten me on how to do this with sql? If not, maybe use php to display the result?

SELECT tbl_operations.OpId,
    tbl_operations.OpDate ,
    COUNT(tbl_operations.OpId) AS `count`
FROM tbl_operations
WHERE MONTH(OpDate)=1
GROUP BY  tbl_operations.OpId

Answer 1

Here’s a quick example. The first part just creates an array of arrays which simulates what you’d get from the database.

The gist is that $counts is an array with a unique OpID for a keys. The values for those arrays are sub-arrays with keys of the month and values of how many times they are found.

Display should just be a simple loop again, however you might want to sort this.

$rows = [
['3',   '2022-01-03'],
['5,3', '2022-01-15'],
['4',   '2022-01-27'],
['5',   '2022-02-01'],
['7',   '2022-02-09'],
['3,2', '2022-01-16'],
];

$counts = [];
foreach($rows as $row){
    $ids = explode(',', $row[0]);
    $month = date('m', strtotime($row[1]));
    foreach($ids as $id){
        if(!array_key_exists($id, $counts)){
            $counts[$id] = [];
        }
        if(!array_key_exists($month, $counts[$id])){
            $counts[$id][$month] = 0;
        }
        
        $counts[$id][$month]++;
    }
}

Demo here: https://3v4l.org/mVaBB

edit

From @mickmackusa, you can shorten the inner loop by using isset:

        if(!isset($counts[$id][$month])){
            $counts[$id][$month] = 0;
        }

See their comment for a demo link

Answer 2

If you're going to query the data in PHP, you might as well return a better result to work with in the first place:

SQL

SELECT GROUP_CONCAT(OpId), MONTH(OpDate)
FROM tbl_operations
GROUP BY MONTH(OpDate)

PHP

// Result from MySQL query
$rows = [
    ['3,5,3,4,3,2', 1],
    ['5,7', 2]
];

And you can perform a count of those grouped results like this:

$results = [];
foreach ($rows as $row) {
    $counts = array_count_values(explode(',', $row[0]));
    $results[$row[1]] = $counts;
}

Result

Array
(
    [1] => Array
        (
            [3] => 3
            [5] => 1
            [4] => 1
            [2] => 1
        )

    [2] => Array
        (
            [5] => 1
            [7] => 1
        )

)

---

What you really want to do though is normalise your data, then you can do this easily in SQL alone.

Answer 3

If you are using at least MYSQL8 and you are not going to normalize your table design, then you can actually use the following CTE query to split, group, format, and sort your result set (no PHP processing).

This approach makes recursive calls on the denormalized table and progressively isolates the rightmost id from comma-delimited values and generates new rows for the individual id values. The recursion continues until there are no commas left.

This solution is built on top of the basic technique demonstrated here.

SQL: (Demo)

WITH RECURSIVE norm AS (
    SELECT OpId,
           OpDate
    FROM tbl_operations
    UNION ALL
    SELECT REGEXP_REPLACE(OpId, '^[^,]*,', '') AS OpId,
           OpDate
    FROM norm
    WHERE OpId LIKE '%,%'
)
SELECT Id,
       Mo,
       COUNT(*) AS Cnt
FROM (
    SELECT REGEXP_REPLACE(norm.OpId, ',.*', '') AS Id,
           MONTH(norm.OpDate) AS Mo
    FROM norm
) formatted
GROUP BY formatted.Id, 
         formatted.Mo

Result Set:

Id	Mo	Cnt
2	1	1
3	1	3
4	1	1
5	1	1
5	2	1
7	2	1

That said, this is a lot of unnecessary voodoo mumbo jumbo for a task that is mega-easy once you've normalized your table --- just normalize it A.S.A.P.