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...
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…