Database SQL query group results by first column

Question

I need help with a query, I want to group result rows by first two columns and I have no idea how to proceed.

My query

 SELECT student_number, homework_name, homework_points 
   FROM student, homework 
   WHERE student.studentid = homework.studentid

Below are my query results

student_number  homework_name  homework_points
--------------  -------------  ---------------
7554            Homework 1     22.5 
7554            Homework 2     16.0
7677            Homework 1     36.0
7677            Homework 2     14.5

How would I go about transforming the query to get results like this

student_number  Homework 1     Homework 2
--------------  -------------  ---------------
7554            22.5           16.0 
7677            36.0           14.5

Any help would be greatly appreciated.

Thanks!

Seriously consider handling issues of data display in application code — Strawberry, Aug 12 '18 at 19:02

Zaynul Abadin Tuhin · Answer 1 · 2018-08-12T16:20:40.957

use case when

        SELECT student_number, 
        max(case when homework_name='Homework 1' then homework_points end) as Homework1,
       max(case when homework_name='Homework 2' then homework_points end) as Homework2
        FROM student inner join homework 
       on student.studentid = homework.studentid 
      group by student_number

http://sqlfiddle.com/#!9/c33289/2

id    Homework1  Homework2
7554    22.5     16
7677    36       14

score 0 · Answer 2 · answered Aug 12 '18 at 16:03

SELECT s.student_number, 
       sum(case when h.homework_name = 'Homework 1' then h.homework_points else 0 end) as Homework_1,
       sum(case when h.homework_name = 'Homework 2' then h.homework_points else 0 end) as Homework_2
FROM student s
JOIN homework h ON s.studentid = h.studentid
GROUP BY s.student_number

score 0 · Answer 3 · answered Aug 12 '18 at 16:07

You could use a couple of join for the table homework

   SELECT s.student_number
      , a.homework_points AS  `Homework 1`
      ,  b.homework_points AS  `Homework 21`
   FROM student s
   LEFT JOIN homework a ON s.studentid = a.studentid 
          and a.homework_name =' Homework 1' 
   left JOIN homework b ON s.studentid = b.studentid 
        and b.homework_name =' Homework 2'
    ORDER BY s.student_number

score 0 · Answer 4 · answered Aug 12 '18 at 16:11

Firstly, you really should be using a proper join rather than a where clause to do your join:

SELECT student_number, homework_name, homework_points
FROM student join homework on student.studentid = homework.studentid

Re your actual question, try this:

select student_name,
h1.homework_points as homework_1,
h2.homework_points as homework_2
from student s
left join homework h1
  on s.studentid = h1.studentid and h1.homework_name = 'Homework 1'
left join homework h2
  on s.studentid = h2.studentid and h2.homework_name = 'Homework 2'
where h1.homework_id is not null or h2.homework_id is not null

Note use of left join on the basis that a particular student may have done only one of the two homeworks.

score 0 · Answer 5 · answered Aug 12 '18 at 18:04

0

I think other answers are correct.

However, I feel like you aren't looking for a hard-coded column list. If this is what you want, then you are looking for a "Pivot" SQL statement.

There are some implementations in SQL Server (as pivot) or in PostgreSQL (as crosstab), but I haven't seen any implementation in MySQL or MariaDB.

answered Aug 12 '18 at 18:04

The Impaler

45,731
9
39
76

There are dozens of questions here on SO that have answers how to do pivoting in mysql. This question is asked on a daily basis. – Shadow Aug 12 '18 at 21:08

Database SQL query group results by first column

5 Answers5