Home » Developer & Programmer » Reports & Discoverer » Sum with term wise.
Sum with term wise. [message #632424] Fri, 30 January 2015 07:09 Go to next message
shahzad-ul-hasan
Messages: 615
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 2020 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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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
Re: Sum with term wise. [message #632426 is a reply to message #632425] Fri, 30 January 2015 07:43 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
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).
Re: Sum with term wise. [message #632427 is a reply to message #632426] Fri, 30 January 2015 07:52 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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.
Re: Sum with term wise. [message #632428 is a reply to message #632427] Fri, 30 January 2015 08:06 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
Registered: August 2002
Senior Member
please view the test case.
  • Attachment: testcase.txt
    (Size: 3.26KB, Downloaded 1814 times)
Re: Sum with term wise. [message #632429 is a reply to message #632428] Fri, 30 January 2015 08:25 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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.
Re: Sum with term wise. [message #632432 is a reply to message #632424] Fri, 30 January 2015 10:46 Go to previous messageGo to next message
John Watson
Messages: 8922
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.
/forum/fa/12483/0/
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 Go to previous messageGo to next message
shahzad-ul-hasan
Messages: 615
Registered: 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 Go to previous message
John Watson
Messages: 8922
Registered: 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: Thu Mar 28 08:29:22 CDT 2024