CODEWITHSUNDEEP
mysqlgroup-bygrouping
mmSQL
mmSQL

Reputation: 61

How to group similar records by single value

I have an issue where i wrote a script that finds students that are in more then one school, and im getting duplicate records for each class under that school.

The problem is that the students share the same unique primary key, so i can't group by that. And i can't group by school or class as that would eliminate other students.

Basically i want to group all records for each student according to school, so that there is only 1 record per school for each student.

I don't really care what class shows for the grouping, as i really want to find students that are listed in more then one school.

For example

id    first    Last    Class School
22     mike     bundy    1     tan
22     mike     bundy    2     tan
22     mike     bundy    3     light

I want to group it down to this

id    first    Last    Class School
22     mike     bundy    1     tan
22     mike     bundy    3     light

Upvotes: 0

Views: 68

Answers (1)

asim-ishaq
asim-ishaq

Reputation: 2230

You can simple use the group by clause to get single record for each school. Like

SELECT
        user.user_id,
        user.last_name,
        user.first_name,
        student.sis_id,
        organization.`NAME`AS school,
        count(*) as Count
    FROM
        user
    JOIN student ON user.user_id = student.user_id
    JOIN x_user_group ON _user.user_id = x_user_group.user_id
    JOIN group ON _x_user_group.group_id = group.group_id
    JOIN organization ON group.owner_id = organization.organization_id
Group by
        user.user_id,
        user.last_name,
        user.first_name,
        student.sis_id,
        school
Order by
        user.last_name,
        user.first_name

The aggregate function count is requried as group by is used. Just ignore it in result set. Now you will have one student in each school.

Upvotes: 1

Related Questions