Question 403571 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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:

id date ustd
1 2024-08-22 1
2 2024-08-22 1
3 2024-08-22 2
4 2024-08-22 2
5 2024-08-22 3

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:

id date ustd count
1 2024-08-22 1 2
3 2024-08-22 2 2
5 2024-08-22 3 1

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');
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    You just need to group by, like

    SELECT min(id) as id, min(date) as date,ustd COUNT(sus) FROM {lbtb_schulzentrum} GROUP BY ustd
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search