Help needed in forming a query

niths_86 · #1 (**permalink**) 07-02-09, 02:29

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

nazirchr · #2 (**permalink**) 07-02-09, 04:00

Hi,

Please use a self outer join for the same

r937 · #3 (**permalink**) 07-02-09, 07:25

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

niths_86 · #4 (**permalink**) 07-02-09, 07:47

wow...thanks a lot, it is working perfectly fine!!

Internet Services

Web Development

Digital Marketing

Consumer Tech