Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
594 views
in Technique[技术] by (71.8m points)

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

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:

enter image description here

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.)


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...