Counting Grouped Data in Moodle

I read some data from the database. To do this, I group the values ​​according to the "date" and the "ustd".

Can I have it count beforehand how many "ustd" it has summarized?

I read the data as follows:

$lbtbs = $DB->get_records('lbtb_schulzentrum', null, 'date,ustd', 'id,date,ustd,eintrag,sus,bgcolor');

Then I run it through a foreach loop:

foreach ($lbtbs as $lbtb) {
    $data[$lbtb->date][$lbtb->ustd] = array("date" => $lbtb->date, "id" => $lbtb->id, "start" => $lbtb->ustd, "description" => $lbtb->eintrag, "sus" => $lbtb->sus, "color" => $lbtb->bgcolor);
}

Briefly describe what I want: I have the following records:

The first two records have the same date and the same ustd number. This means that I want to group the two records. This works so far. However, I would also like to pass on the "2" (count the records: 2 records = output 2, 3 records = output 3).

My output that I want to have:

Edit:

I tried using COUNT. Unfortunately, the data that is counted is no longer output.

$lbtbs = $DB->get_records_sql('SELECT id,date,ustd,bgcolor,sus, COUNT(sus) FROM {lbtb_schulzentrum}');

$data = array();

foreach ($lbtbs as $lbtb) {
    $data[$lbtb->date][$lbtb->ustd] = array("date" => $lbtb->date, "id" => $lbtb->id, "start" => $lbtb->ustd, "color" => $lbtb->bgcolor);
}

Solution: The GROUP BY was missing and COUNT(*) AS count to access the value.

$lbtbs = $DB->get_records_sql('SELECT id,date,ustd,bgcolor,sus, COUNT(*) AS count FROM {lbtb_schulzentrum} GROUP BY date,ustd');

Always let the database do the work, it is faster and more efficient than using PHP code

SQL Aggregate

COUNT() is an aggregate function, like SUM(), MIN(), MAX()

When using aggregate functions, you need to use a GROUP BY column names

In your case this will be something like

GROUP BY date, ustd

You will need to use a group by on every column in your SELECT

It's also useful to use an alias for the count column eg:

COUNT() AS mycount

Unique column

In Moodle, the first column in the $DB->get_records_sql() function needs to be a unique value

The id column in the lbtb_schulzentrum table is a unique value, but a bit pointless for a GROUP BY because it will return every row

So use concat to join the date and ustd columns to make a unique id. In addition, Moodle can use several different databases, so use the compatibility function $DB->sql_concat()

$DB->sql_concat('date', 'ustd')

See https://moodledev.io/docs/4.4/apis/core/dml#sql_concat

Final code

So your final sql should look like

$sql = "SELECT " . $DB->sql_concat('date', 'ustd') . " AS uniqueid, date, ustd, COUNT() AS mycount
        FROM {lbtb_schulzentrum}
        GROUP BY date, ustd";

$lbtbs = $DB->get_records_sql($sql);

Column names

And just a tip, date is a reserved column name

https://docs.moodle.org/dev/XMLDB_reserved_words

So if your code is used on different databases, it might break

When creating tables, I would recommend using the XMLDB Editor

Go to Site Admin > Development > XMLDB Editor