OraFAQ Forum: Reports & Discoverer » Sum with term wise.

Home » Developer & Programmer » Reports & Discoverer » Sum with term wise.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sum with term wise. [message #632424]

Fri, 30 January 2015 07:09

shahzad-ul-hasan
Messages: 643
Registered: 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.
/forum/fa/12483/0/

Attachment: 22.JPG
(Size: 29.31KB, Downloaded 2925 times)

[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: 13963
Registered: 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

Report message to a moderator

Re: Sum with term wise. [message #632426 is a reply to message #632425]

Fri, 30 January 2015 07:43

shahzad-ul-hasan
Messages: 643
Registered: 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).

Report message to a moderator

Re: Sum with term wise. [message #632427 is a reply to message #632426]

Fri, 30 January 2015 07:52

cookiemonster
Messages: 13963
Registered: 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.

Report message to a moderator

Re: Sum with term wise. [message #632428 is a reply to message #632427]

Fri, 30 January 2015 08:06

shahzad-ul-hasan
Messages: 643
Registered: August 2002

Senior Member

please view the test case.

Attachment: testcase.txt
(Size: 3.26KB, Downloaded 2311 times)

Report message to a moderator

Re: Sum with term wise. [message #632429 is a reply to message #632428]

Fri, 30 January 2015 08:25

cookiemonster
Messages: 13963
Registered: 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.

Report message to a moderator

Re: Sum with term wise. [message #632432 is a reply to message #632424]

Fri, 30 January 2015 10:46

John Watson
Messages: 8964
Registered: 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.

Report message to a moderator