Databases
StackOverflow2013
Postsdbo
POHow to write this complex SQL statement
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to write this complex SQL statement
    primarykey
    data
    text
    <p>I have three identical tables in my MySQL table namely</p> <p><strong>first_term_result, second_term_result and third_term_result</strong></p> <p>this are the columns in it</p> <pre><code>exam_type_id | student_id | subject_id | mark | </code></pre> <p>or example with dummy data</p> <p><strong>NOTE:</strong> there are three different exam type for each subjects (CA1, CA2, CA3 and Exam), there are three table like this with same thing but different data as it hold data for first term another for second term and the last for third term.</p> <pre><code>first_term_result: exam_type_id | student_id | subject_id | mark | 1 | 6 | 7 | 12 | 2 | 6 | 7 | 9 | 3 | 6 | 7 | 13 | 4 | 6 | 7 | 45 | 1 | 4 | 7 | 7 | 2 | 4 | 7 | 5 | 3 | 4 | 7 | 10 | 4 | 4 | 7 | 34 | second_term_result: exam_type_id | student_id | subject_id | mark | 1 | 6 | 7 | 15 | 2 | 6 | 7 | 6 | 3 | 6 | 7 | 10 | 4 | 6 | 7 | 50 | 1 | 4 | 7 | 6 | 2 | 4 | 7 | 3 | 3 | 4 | 7 | 9 | 4 | 4 | 7 | 44 | third_term_result: exam_type_id | student_id | subject_id | mark | 1 | 6 | 7 | 17 | 2 | 6 | 7 | 8 | 3 | 6 | 7 | 15 | 4 | 6 | 7 | 67 | 1 | 4 | 7 | 12 | 2 | 4 | 7 | 8 | 3 | 4 | 7 | 12 | 4 | 4 | 7 | 50 | </code></pre> <p>Now what i want to achieve is get the <code>SUM()</code> of <code>first_term_result.mark</code> <code>second_term_result.mark</code> and <code>third_term_result.mark</code> of each students <code>WHERE</code> subject_id=7 group by students name.</p> <p>another very important problem is i will be calculating the grand sum for each students for first_term+second_term+third_term and also want to be able to order the grand total for that student and the subjects in DESC so i can position them accordingly please if it will be easier on php please let me know.</p> <p>Thanks</p> <p>it seems very complex to me but i know there are gurus here who ca achieve this, i read somewhere that it is possible to order by even when rollup is used.</p> <p>below is my code which doesn't work obviously.</p> <pre><code>SELECT CONCAT(s.fname,' ',s.mname,' ',s.lname) AS sname, SUM(f.mark) AS first_total, SUM(se.mark) AS second_total, SUM(t.mark) AS third_total SUM(f.first_total,second.total,third_total) as GT // just to show my intention FROM students s, first_term_result f, second_term_result se, third_term_result t WHERE s.studentID=f.student_id AND s.studentID=se.student_id AND s.studentID=t.student_id AND f.subject_id=7 AND se.subject_id=7 AND t.subject_id=7 GROUP BY sname ORDER BY GT DESC </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USLinger
    1. USuser2666633
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to write this complex SQL statement
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIt will be far less complex if you further normalize your data. Make a column for term and keep all terms in the same table.
      singulars
      1. POHow to write this complex SQL statement
      1. USD Mac
    2. CO@DMac seriously this has made me dumb, can seem to know how i can achieve same if i keep all terms on same table
      singulars
      1. POHow to write this complex SQL statement
      1. USuser2666633
    3. CO*complex* :D We use queries with up to 200 lines..
      singulars
      1. POHow to write this complex SQL statement
      1. USDanFromGermany
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload