Results 1 to 10 of 10

Thread: Danish Query 1

  1. #1
    Join Date
    Oct 2010
    Posts
    6

    Unanswered: Danish Query 1

    I have three tables

    student has
    id name code
    1 Danish Sci
    2 Hari Sci

    subject has
    id name
    1 Maths
    2 Science

    Marks has
    id student_id subject_id marks
    1 1 1 70
    2 1 2 80
    3 2 1 90
    4 2 2 95

    i want result set below in one query

    Danish Maths 70 150
    Danish Science 80 150
    Hari Maths 90 185
    Hari Science 95 185

    Thanks in advance

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Doesn't seem to be too difficult. What did you manage to do so far by yourself?

  3. #3
    Join Date
    Oct 2010
    Posts
    6
    SELECT student.name,subject.name ,v3.total
    FROM
    student,subject,marks,
    (SELECT * ,SUM(marks.marks) as Total
    FROM marks,student ,subject
    where student.id=marks.student_id
    and
    marks.subject_id IN (SELECT subject.id FROM subject)
    group by student.id,subject.id) as v3
    where subject.id = marks.subject_id
    AND student.id = marks.student_id

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    This is an invalid query, it wouldn't compile. Please, post something that WORKS (maybe it doesn't return the result you need, but it MUST NOT fail to execute).

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Seems like an easy exercise about using analytic function, or more difficult one for emulating it.
    Code:
    SELECT st.name, sb.name, m.marks, <analytic SUM> total
    FROM marks m
      INNER JOIN student st ON m.student_id = st.id
      INNER JOIN subject sb ON m.subject_id = sb.id;
    Analytic functions are described in SQL Reference book. It is available with other Oracle documentation e.g. online on http://tahiti.oracle.com/ (or just go through your student book).

    If you want to emulate it, think about logic - instead of querying only aggregates, it would be better to count it separately (probably only on MARKS table) and then join with the original table.

  6. #6
    Join Date
    Oct 2010
    Posts
    6
    Hii Thanks It works fine.
    Sorry i couldn't reply u before.
    But now i have to print a matrix report .
    Maths Chemistry Total
    Danish 40 50 90
    Subhu 50 60 20

    I am new to matrix report plz help me.
    Thanks once again

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    A matrix report? What tool are we talking about?

  8. #8
    Join Date
    Oct 2010
    Posts
    6
    no tool only basic sql cmd prompt in oracle 10 g ,is it possible to get a layout in the format
    Maths Chemistry Total
    Danish 40 50 90
    Subhu 50 60 20

  9. #9
    Join Date
    Oct 2010
    Posts
    6
    i need a crosstab query
    i have to hard code the subjects on the column heading or there is some other way
    Maths Chemistry Total
    Danish 40 50 90
    Subhu 50 60 20

  10. #10
    Join Date
    Oct 2010
    Posts
    6
    hey anyone plz help me

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •