Query not running properly while having sum function

Question

I am running a query:

select 
    iars.id,students.rollno,
    students.name as name, 
    teachers.name as tname, 
    students.studentid, 
    t1.studentid,
    sum(t1.obt) as obt1, sum(t1.benefits) as ben1, sum(t1.max)  as max1,
    (t2.obt) as obt2, (t2.benefits) as ben2, (t2.max)  as max2,
    (t3.obt) as obt3, (t3.benefits) as ben3, (t3.max)  as max3,
    (t4.obt) as obt4, (t4.benefits) as ben4, (t4.max)  as max4,
    (t5.obt) as obt5, (t5.benefits) as ben5, (t5.max)  as max5 
from groups,students
left join iars on iars.id
left join str on str.studentid=students.studentid
left join course on course.c_id=students.course 
left join teachers on teachers.id=iars.teacherid
join sgm on sgm.studentid=students.studentid
left join semdef on month
left join sps on sps.studentid=students.studentid and iars.paperid=sps.paperid
left join `attndata` `t1` on ((t1.studentid=students.studentid) and iars.id=t1.iarsid and t1.mon=1)
left join `attndata` `t2` on ((t1.studentid=t2.studentid) and t2.mon = 2 and iars.id=t2.iarsid)
left join `attndata` `t3` on ((t2.studentid=t3.studentid) and t3.mon = 4 and iars.id=t3.iarsid)
left join `attndata` `t4` on ((t3.studentid=t4.studentid) and t4.mon = 5 and iars.id=t4.iarsid)
left join `attndata` `t5` on ((t4.studentid=t5.studentid) and t5.mon = 6 and iars.id=t5.iarsid)
where students.course='1' and students.status='regular' and sps.paperid='2' and 
iars.courseid=students.course and iars.semester=str.semesterid and iars.paperid='2' and
str.semesterid='1' and str.sessionid='12' and groups.id=sgm.groupid 
group by sps.studentid , teachers.id order by students.name

It's with grouping, like I want to have sum according to the months, but whenever I use sum, it gives me funny result.

Farhan Syed · Accepted Answer · 2012-11-14 19:21:00Z

1

If you want the sum by months, you should group by date with month. Grouping with student and teacher will give you more than one row per month and will result in funny result

answered Nov 14, 2012 at 19:21

Farhan Syed

3263 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Farhan Syed Over a year ago

Of course, group by date, teacher, or group by teacher, date depending on what your base report needs to be.

I Perfect Over a year ago

I changed it to teacher and then date, but it is still not coming up properly.

Barmar Over a year ago

Please edit the question. Show what you want to get and what you're getting instead.

Collectives™ on Stack Overflow

Query not running properly while having sum function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related