I have this table in a database

I need to retrive data to display like this in a html table

I'm new in SQL Queries I tried alot to run different sql command but i did not succeeded. I need your help.

my last query was this:

SELECT DISTINCT(subject),score FROM `examresults` WHERE stid='STD-1' GROUP BY subject,term

CodePudding user response：

As the original table depicts, each subject has only one value for a particular term. So it's safe to use the sum() function in the select list in accordance with the group by clause to get the one and only value for a particular term of a particular subject. Try this:

select subjects as 'SUBJECT', 
sum(case term when 'Term 1' then score end )as 'TERM ONE',
sum(case term when 'Terminal' then score end )as 'TERMINAL',
truncate(avg(score),2) as 'AVERAGE'
from examresults
group by subjects
;

CodePudding user response：

Either SELECT * FROM tbl and group with php:

$data = [];

foreach ($db->prepare('SELECT * FROM tbl')->fetchAll() as $row) {
    $data[$row['subjects']][$row['term']] = $row['score'];
    // Calculate average after all results are gathered
}

OR with sub queries:

SELECT subjects AS SUBJECT,
    (SELECT t1.score FROM tbl t1 WHERE t1.term = 'Term 1' AND t1.subjects = t.subjects) AS 'TERM ONE',
    (SELECT t2.score FROM tbl t2 WHERE t2.term = 'Terminal' AND t1.subjects = t.subjects) AS 'TERMINAL',
    (SELECT t3.score FROM tbl t3 WHERE t3.term = 'Term 1' AND t3.subjects = t.subjects)   (SELECT t4.score FROM tbl t4 WHERE t4.term = 'Terminal' AND t2.subjects = t.subjects) / 2 AS 'AVERAGE'
FROM tbl t