mysql - How to use GROUP BY in a query while using variables

Question

I'm using MySql V5.7. I have a table PatientAppointment. I need to GROUP BY CDRId which is a column in the same table. Here's the query:

SELECT AppointmentDateTime,
       duration,
       minutes,
       @prev_month := BillingMonth BillingMonth,
       @prev_total := total total
FROM (select AppointmentDateTime,
             duration,
             @cur_dur := ((case when duration like '% hour%' then substring_index(duration, ' hour', 1) * 60 else 0 end) +
                         (case when duration like '%min%' then substring_index(substring_index(duration, ' min', 1), ' ', -1) + 0 else 0 end)) as minutes,

             CASE WHEN @year_month = date_format(AppointmentDateTime, '%Y-%m')
                  THEN @cum_sum := @cum_sum + @cur_dur
                  ELSE @cum_sum := @cur_dur
                  END total,
             @year_month := date_format(AppointmentDateTime, '%Y-%m') BillingMonth

      from PatientAppointment, (SELECT @year_month:='', @cum_sum:=0, @cur_dur:=0) variables
      GROUP BY CDRId  <------ ERROR
      ORDER BY AppointmentDateTime) subquery, 
(SELECT @prev_month:=0, @prev_total:=0) variable
ORDER BY AppointmentDateTime, total

Here is a working db<>fiddle. Please help me. I want the entire result set to be grouped by CDRId

If we cannot use GROUP BY clause here, can we do it some logic changes here.

This is the type of grouping I want. See this:

I'm getting this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server v...

Answer 1

Unfortunately, SQL has no way of "merging table cells" like you show in your screenshot. SQL is not like a spreadsheet.

You must fetch the rows and then work out in application code how you want to group them.

(You asked me on another thread to come here and give an answer.)