$select = $pdo->prepare("select * from tbl_class c

          LEFT JOIN ( SELECT titile,subpid,sub_name, FROM tbl_subject)s ON s.subpid=c.subfkey

          LEFT JOIN ( SELECT markpid,mark_studetpid,mark_year,mark_subpid,mark_mark,mark_category FROM tbl_mark)m 
          ON c.studentfkey=m.mark_studetpid

          where $id=c.studentfkey and s.subpid=m.mark_subpid and m.mark_category ='A' order by m.mark_year desc ");

the output is like this now

But i would like to have the results like this Grouping By year and subject and sum of marks yearly for each subjects

I tried to add group by m.mark_year,m.mark_subpid but the sum is not counting well.

CodePudding user response：

If grouping within the query is not desired, try making it with PHP (not recommended for big data).

First make an array to sum up marks on iterations

while ($row = mysql_fetch_object ($response)) {
   $marks[$row->classId][$row->year]  = $row->mark;
}

Then use it to build up the table.

PS: sorry for showing mysql function, just forgot how to make the same in PDO. But it's an example.

CodePudding user response：

--drop table #Maths (Active it to run in SQL for multiple times)
create table #Maths
(
ClassID int,
SubjectName nvarchar(100),
MarkYear int,
Mark int (change to decimal(12,2) if needed)
)
--drop table #Chemistry (Active it to run in SQL for multiple times)
create table #Chemistry
(
ClassID int,
SubjectName nvarchar(100),
MarkYear int,
Mark int (change to decimal(12,2) if needed)
)

insert into #Maths
select ClassID, SubjectName, MarkYear, sum(Mark) from tbl_class a
LEFT JOIN tbl_subject B ON B.subpid=A.subfkey
LEFT JOIN tbl_mark C ON A.studentfkey=C.mark_studetpid
where SubjectName = 'Maths'

insert into #Chemistry
select ClassID, SubjectName, MarkYear, sum(Mark) from tbl_class a
LEFT JOIN tbl_subject B ON B.subpid=A.subfkey
LEFT JOIN tbl_mark C ON A.studentfkey=C.mark_studetpid
where SubjectName = 'Chemistry'

select ClassID, SubjectName, MarkYear, Mark from #Maths
union all
select ClassID, SubjectName, MarkYear, Mark from #Chemistry
order by MarkYear desc

try this. just try change with your own Columns or Tables name