Merge rows in mysql based on condition-CodePudding

I am trying to merge the rows based on condition in mysql.

I have table as shown below :

Looking merge the row 1 into row 2 (where the attendance count is larger)

and need to shown the result as :

I was trying to divide the dataset into 2 parts using the below query

select 
a.student_id,a.school_id,a.name,a.grant,a.classification,a.original_classification,,a.consent_type
from (
select * from school_temp where original_classification='all' and availability='implicit')a
join(select * from school_temp where original_classification!='all' and availability!='implicit')b
on a.student_id = b.student_id and a.school_id=b.school_id and a.name=b.name

But unable to merge the rows and get total attendance count . Please help me ,i am badly stuck in this

CodePudding user response：

Split this into two queries that you combine with UNION.

The first joins the implicit row with the row with the highest attendance among the explicit rows for each student. See Retrieving the last record in each group - MySQL for how that works. Use SUM(attendance_count) to combine the attendances.

The second query in the UNION gets all the rows that don't have the highest attendance.

WITH explicit as (
    SELECT *
    FROM school_temp
    WHERE original_classification!='all' and availability!='implicit'
)

SELECT a.student_id, a.school_id, a.name, SUM(attendance_count) AS attendance_count,
    b.grant, b.classification, b.original_classification, b.consent_type
FROM school_temp AS a
JOIN (
    SELECT t1.*
    FROM explicit AS t1
    JOIN (
        SELECT student_id, school_id, name, MAX(attendance_count) AS max_attendance
        FROM explicit AS t2
        GROUP BY student_id, school_id, name
    ) AS t2 ON t1.student_id = t2.student_id AND t1.school_id = t2.school_id AND t1.name = t2.name AND t1.attendance_count = t2.max_attendance
) AS b ON a.student_id = b.student_id and a.school_id=b.school_id and a.name=b.name 
WHERE a.original_classication = 'all' AND a.availability = 'implicit'

UNION ALL

SELECT t1.*
FROM explicit AS t1
JOIN (
    SELECT student_id, school_id, name, MAX(attendance_count) AS max_attendance
    FROM explicit AS t2
    GROUP BY student_id, school_id, name
) AS t2 ON t1.student_id = t2.student_id AND t1.school_id = t2.school_id AND t1.name = t2.name AND t1.attendance_count < t2.max_attendance

I've used a CTE to give a name to the subquery that gets all the explicit rows. If you're using MySQL 5.x, you'll need to replace explicit with that subquery throughout the query. Or you could define it as a view.