Home » Developer & Programmer » Reports & Discoverer » Sum with term wise.
Sum with term wise. Fri, 30 January 2015 07:09
 shahzad-ul-hasan Messages: 600Registered: August 2002 Senior Member
i want to display this type of report as shown in the figure
``` select sum(test.totmarks),sum(obtmarks) from test1,test,student
where test1.tstuid=1352
and status='PRESENT'
And TEST1.TID=TEST.TID
AND STUDENT.CLASS='EIGHT'
AND EXAMC='1ST ASSESSMENT OF 2ND TERM'
AND TEST1.TDATE=TEST.TDATE
AND TEST.SEC='WHITE'
/

TEST.TOTMARKS) SUM(OBTMARKS)
-------------- -------------
7980          7619 ```

The actual result of above query is 450. but it shows this please advised. 1st term, 2nd term belongs to examc.
• Attachment: 22.JPG

[Updated on: Fri, 30 January 2015 07:10]

Report message to a moderator

Re: Sum with term wise. [message #632425 is a reply to message #632424] Fri, 30 January 2015 07:35
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
shahzad-ul-hasan wrote on Fri, 30 January 2015 13:09

The actual result of above query is 450.

No the actual result of the query is 7980 and 7619, obviously.
If you think both should be 450 then either:
a) Your where clause is wrong
b) The separate calculation you did to determine the answer is 450 is wrong.

For us to determine which of those is the case we would need:
1) The raw data that's being summed.
2) A description of the relationship between the tables
Re: Sum with term wise. [message #632426 is a reply to message #632425] Fri, 30 January 2015 07:43
 shahzad-ul-hasan Messages: 600Registered: August 2002 Senior Member
Test is master Table with fields primary key (Tid,Tdate) and test1 is the Detail table (Tid,Tdate) foreign key. Student Table has Primary key (Stuid).
Re: Sum with term wise. [message #632427 is a reply to message #632426] Fri, 30 January 2015 07:52
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
That doesn't describe all the columns used in the where clause.
It tells us nothing about what sort of data test and test1 hold or how to identify the data you want.
It doesn't tell us what data you have.

So it tells us nothing useful.
As usual you need to supply a proper test case.
Re: Sum with term wise. [message #632428 is a reply to message #632427] Fri, 30 January 2015 08:06
 shahzad-ul-hasan Messages: 600Registered: August 2002 Senior Member
• Attachment: testcase.txt
Re: Sum with term wise. [message #632429 is a reply to message #632428] Fri, 30 January 2015 08:25
 cookiemonster Messages: 13894Registered: September 2008 Location: Rainy Manchester Senior Member
Oh good grief.
I run your query against that data and I get null.
Do I really need to tell you that data that actually recreates the problem is required?

That said - your query has a cartesian join.
Re: Sum with term wise. [message #632432 is a reply to message #632424] Fri, 30 January 2015 10:46
 John Watson Messages: 8738Registered: January 2010 Location: Global Village Senior Member
shahzad-ul-hasan wrote on Fri, 30 January 2015 13:09
i want to display this type of report as shown in the figure
``` select sum(test.totmarks),sum(obtmarks) from test1,test,student
where test1.tstuid=1352
and status='PRESENT'
And TEST1.TID=TEST.TID
AND STUDENT.CLASS='EIGHT'
AND EXAMC='1ST ASSESSMENT OF 2ND TERM'
AND TEST1.TDATE=TEST.TDATE
AND TEST.SEC='WHITE'
/

TEST.TOTMARKS) SUM(OBTMARKS)
-------------- -------------
7980          7619 ```

The actual result of above query is 450. but it shows this please advised. 1st term, 2nd term belongs to examc.
You have omitted a join condition to STUDENT, so you are cross joining to it and therefore producung a rubbish result. This is yet another example (the second this week) of the problems caused when people do not use ANSI join syntax. If you use AMSI syntax, such a mistake is impossible.

Re: Sum with term wise. [message #632512 is a reply to message #632432] Mon, 02 February 2015 04:37
 shahzad-ul-hasan Messages: 600Registered: August 2002 Senior Member
thanks for your help. i have done it.
Re: Sum with term wise. [message #632513 is a reply to message #632512] Mon, 02 February 2015 04:40
 John Watson Messages: 8738Registered: January 2010 Location: Global Village Senior Member
Forum etiquette! Please show your solution, so that it can help others (as they have helped you).

[Updated on: Mon, 02 February 2015 04:41]

Report message to a moderator

 Previous Topic: drill down reports in oracle reports10g Next Topic: Align Graph
Goto Forum:

Current Time: Sun Aug 14 22:49:58 CDT 2022