*************

SQL code: Upload Copy Format Clear

CREATE TABLE visits ( id INT AUTO_INCREMENT PRIMARY KEY, Tidspunkt DATE NOT NULL, Klubtype VARCHAR(10) NOT NULL, Handling INT NOT NULL ); INSERT INTO visits (Tidspunkt, Klubtype, Handling) VALUES ('2024-01-05', 'FK', 1), ('2024-01-10', 'FK', 1), ('2024-01-15', 'FK', 1), ('2024-01-20', 'UK', 1), ('2024-01-25', 'UK', 1), ('2024-02-05', 'FK', 1), ('2024-02-15', 'FK', 1), ('2024-02-25', 'UK', 1);

SQL Server: Preserve result

Result: Copy Clear

PHP code: Copy Format Clear

<?php $danishMonths = [ 1 => 'Januar', 2 => 'Februar', 3 => 'Marts', 4 => 'April', 5 => 'Maj', 6 => 'Juni', 7 => 'Juli', 8 => 'August', 9 => 'September', 10 => 'Oktober', 11 => 'November', 12 => 'December' ]; $sql = <<<SQL SELECT YEAR(Tidspunkt) year, MONTH(Tidspunkt) month_num, Klubtype type, COUNT(Handling) visits, COUNT(Handling) / DAY(LAST_DAY(MIN(Tidspunkt))) avg_visits FROM visits WHERE YEAR(Tidspunkt) = '2024' GROUP BY year, month_num, type ORDER BY year, month_num, type SQL; $month_array = []; foreach ($mysqli->query($sql) as $row) { if (!isset($ref[$row['month_num']])) { $month_array[] =& $ref[$row['month_num']]; } $ref[$row['month_num']][] = [ 'month' => $danishMonths[$row['month_num']], 'visits' => $row['visits'], 'type' => $row['type'], 'avg' => $row['avg_visits'] ]; } var_export($month_array);

PHP version:

Show: Warnings Notice

Result: Copy Clear

PHPize Online / SQLize Online / SQLtest Online