getting invalid use of group function error in query

Question

I am trying to convert the data from student_attendance_daily on the basis of class_id and month id but I am getting error invalid use of group function I cannot use JSON_ARRAYAGG because i am using MariaDB 10.4 but it is available in 10.5 i am using it on AWS RDS and do not have choice to upgrade so i am trying like this.

here is my query

INSERT INTO attendance_student (class_id, month_id, attendance_details, created_at, updated_at)
SELECT 
  class_id, 
  DATE_FORMAT(attendance_date, '%Y%m') AS month_id, 
  CONCAT('[', 
    GROUP_CONCAT(
      CONCAT(
        '{"att_date":"', DATE_FORMAT(attendance_date, '%d-%m-%Y'), '",',
        '"att_status":"', attendance_status, '",',
        '"att_punch":',
        IFNULL(
          CONCAT('[', 
            GROUP_CONCAT(
              CONCAT(
                '{"did":"', bio_punch_details, '",',
                '"pat":"', IFNULL(DATE_FORMAT(created_at, '%d-%m-%Y %H:%i:%s'), ''), '",',
                '"ss":"', IFNULL(bio_punch_details, ''), '"}'
              )
              ORDER BY created_at ASC
              SEPARATOR ','
            ), 
          ']'),
          '[]'
        ),
        '}'
      )
      ORDER BY attendance_date ASC
      SEPARATOR ','
    ) 
  , ']') AS attendance_details, 
  NOW() AS created_at, 
  NOW() AS updated_at
FROM student_attendance_daily
GROUP BY class_id, month_id;

I want to insert data like this

(1, 136, 1, '[{\"att_date\":\"12-01-2023\",\"att_status\":0,\"att_punch\":[{\"did\":\"1\",\"pat\":\"12-01-2023 18:35:33\",\"ss\":\"ss_std_20230112183534_667757.jpg\"}]},{\"att_date\":\"20-01-2023\",\"att_status\":1,\"att_punch\":[{\"did\":\"2\",\"pat\":\"20-01-2023 00:29:48\",\"ss\":\"ss_std_20230120002949_672689.jpg\"}]}]', '2023-01-12 18:35:34', '2023-01-20 00:29:49')

it is attendance_student schema

CREATE TABLE `attendance_student` (
  `id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `month_id` int(11) NOT NULL,
  `attendance_details` mediumtext COLLATE utf8_unicode_ci NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--

and it is student_attendance_daily table schema

CREATE TABLE `student_attendance_daily` (
  `id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `attendance_date` date NOT NULL,
  `attendance_status` tinyint(1) NOT NULL,
  `late_status` tinyint(1) DEFAULT NULL,
  `attendance_remarks` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `bio_punch_details` text COLLATE utf8_unicode_ci,
  `created_at` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  `updated_at` datetime NOT NULL,
  `updated_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Answer 1

You can't put an aggregate function inside an aggregate function in the same query.

You have to use a subquery to generate the inner aggregate, then do the outer aggregate in the outer query. If you have further levels of nesting JSON, you would need further levels of subqueries.

This would apply to JSON_ARRAYAGG() as well if you are someday able to upgrade and use that function.