GROUP BY data store process count DISTINCT values

Aljaz Ketis commented on Jul 4, 2022

How can I achieve that inside of GROUP BY proccess I count only the DISTINCT values?

Sebastian Morales commented on Jul 4, 2022

Can you pls give a specific example (with some pseudo code) of what you want? Tks,

Aljaz Ketis commented on Jul 4, 2022

Something like that

$this->src("db")
            ->query(
                "SELECT date_created, id_of_something
                 FROM table;"
            )
            ->pipe(
                new Group(
                    array(
                        "by"    => "date_created",
                        "count" => "DISTINCT id_of_something",
                    )
                )
            )

Aljaz Ketis commented on Jul 4, 2022

And i want to count the rows of all and the rows of uniue ids with one query.

Sebastian Morales commented on Jul 4, 2022

Unfortunately, the Group process hasn't had a count by distinct value operator yet. We have a similar process called Cube which could do that albeit there's a little bit of code involved:

//MyReport.php
$distinctValues = [];
...
->pipe(new \koolreport\processes\Cube(array(
    "customAggregates" => [
        "distinctCount" => [
            "{initValue}" => 0,
            "{aggValue}" => function($aggValue, $value, $field, $row, $dn) use (& $distinctValues) {
                if (!isset($distinctValues[$dn])) $distinctValues[$dn] = [];
                $columnDistinctValues = $distinctValues[$dn];
                if (!in_array($value, $columnDistinctValues)) {
                    $aggValue++;
                    array_push($distinctValues[$dn], $value);
                }
                return $aggValue;
            },
        ]
    ],
    "row" => "date_created",
    "distinctCount" => "id_of_something"
)))
->pipe($this->dataStore("ds"));

//MyReport.view.php
//print out the datastore's data to see its columns and values
print_r($this->dataStore("ds")->data());

Pls try this and let us know if there's any issue. Tks,

KoolReport's Forum

GROUP BY data store process count DISTINCT values #2747

Build Your Excellent Data Report