Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    9

    Unanswered: Help needed in forming a query

    Hi could anyone kindly help me in forming a query to display as follows:
    Suppose i have a table with 3 columns and values in it as follows :
    subject exam_month marks
    History jan 20
    History feb 40
    Maths jan 40
    English feb 60
    History jan 20

    Now i want to display the total of marks obtained in jan for every sub. the problem i am facing is i need to display the subject name with a value 0 even if the subject is not present in Jan. So my output need to be like
    subject marks
    History 40
    Maths 40
    English 0
    Please help me in forming a query to get the desired output.
    The query i had used is

    select distinct(subject) , isnull( sum(marks), 0) from sample_table where exam_moth = 'jan' group by subject;

    But the output i got is :
    History 40
    Maths 20

    Note: English does not get displayed
    Last edited by niths_86; 07-02-09 at 02:53.

  2. #2
    Join Date
    Jul 2009
    Posts
    1
    Hi,

    Please use a self outer join for the same

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT s.subject
         , SUM(d.marks) AS total_marks
      FROM (
           SELECT DISTINCT subject 
             FROM sample_table
           ) AS s
    LEFT OUTER
      JOIN sample_table
        ON sample_table.subject = s.subject
       AND sample_table.exam_moth = 'jan' 
    GROUP 
        BY s.subject
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2009
    Posts
    9
    wow...thanks a lot, it is working perfectly fine!!

Posting Permissions

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