Home » SQL & PL/SQL » SQL & PL/SQL » total marks (merged)
total marks (merged) [message #397288] Thu, 09 April 2009 22:25 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member

i have two tables: com6101 and com6670.
whenever someone make an entry in com6101 or com6670 for Student with Reg 1, the query should display a sum of the marks based on reg number and year if it exist in both otherwise just show from one table.

Note: Reg+Year is composite primary key.
Com6101
 
Reg     Year    Assigment1 Assigment2   Exam       Total

1       2008    20         25           20         65
2       2008    25         20           25         70
1       2007    25         20           25         70

Com6670

Reg     Year    Assigment1 Assigment2   Quiz1   Quiz2      Total

1       2008    20         25           10      5          60
3       2008    25         20           5       5          55

i need a query to show the following data.

Reg     Year                Total

1       2008                125
1       2007                70
2       2008                70
3       2008                55

regards.
Riaz

[Updated on: Thu, 09 April 2009 23:24] by Moderator

Report message to a moderator

Re: total marks [message #397290 is a reply to message #397288] Thu, 09 April 2009 22:44 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
Show us what you did (if you tried it yourself) and how Oracle responded (COPY & PASTE your SQL*Plus session), including errors and/or why the result is not what you want.
Do not describe, explain or report - show us!

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: total marks (merged) [message #397294 is a reply to message #397288] Thu, 09 April 2009 23:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addtion, post a working Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: total marks (merged) [message #397365 is a reply to message #397288] Fri, 10 April 2009 06:27 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Thank you all. It has been solved.

Re: total marks (merged) [message #397366 is a reply to message #397365] Fri, 10 April 2009 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good, now could you post the solution you got.
It is appreciated someone that asks for help post the solution when he found it to help futur readers.

Regards
Michel
Re: total marks (merged) [message #397374 is a reply to message #397288] Fri, 10 April 2009 07:08 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Here is the code in my case.

SELECT SummativeQ.AcademicId, SummativeQ.ModuleId, SummativeQ.TaughtYear, SummativeQ.TotalHours+FormativeMarkingQ.Total AS Total
FROM FormativeMarkingQ INNER JOIN SummativeQ ON (FormativeMarkingQ.TaughtYear = SummativeQ.TaughtYear) AND (FormativeMarkingQ.ModuleId = SummativeQ.ModuleId) AND (FormativeMarkingQ.AcademicId = SummativeQ.AcademicId);
UNION ALL
SELECT FormativeMarkingQ.AcademicId, FormativeMarkingQ.ModuleId, FormativeMarkingQ.TaughtYear, FormativeMarkingQ.Total AS Total
FROM FormativeMarkingQ LEFT JOIN SummativeQ ON (FormativeMarkingQ.AcademicId = SummativeQ.AcademicId) AND (FormativeMarkingQ.ModuleId = SummativeQ.ModuleId) AND (FormativeMarkingQ.TaughtYear = SummativeQ.TaughtYear)
WHERE (((SummativeQ.TotalHours) Is Null));
UNION ALL SELECT SummativeQ.AcademicId, SummativeQ.ModuleId, SummativeQ.TaughtYear, SummativeQ.TotalHours as Total
FROM FormativeMarkingQ RIGHT JOIN SummativeQ ON (FormativeMarkingQ.TaughtYear = SummativeQ.TaughtYear) AND (FormativeMarkingQ.ModuleId = SummativeQ.ModuleId) AND (FormativeMarkingQ.AcademicId = SummativeQ.AcademicId)
WHERE (((FormativeMarkingQ.Total) Is Null));



however, I used MS ACCESS.


Riaz
Re: total marks (merged) [message #397376 is a reply to message #397374] Fri, 10 April 2009 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A good link: SQL Formatter
As it your query will help no one as no one can read it.

Regards
Michel
Re: total marks (merged) [message #397378 is a reply to message #397374] Fri, 10 April 2009 07:18 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
rzkhan wrote on Fri, 10 April 2009 14:08

however, I used MS ACCESS.

Oh.

I guess it is not a very good idea asking for help on MS Access within the scope of Oracle related forum. Certain features can be shared between these two, but most of them not. So, you might have gotten a solution which would not have worked on MS Access.

Perhaps you should choose a place for your questions more carefully in the future.
Re: total marks (merged) [message #397432 is a reply to message #397288] Fri, 10 April 2009 12:05 Go to previous messageGo to next message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
I think UNION ALL can do it as follow;


Select reg, year, sum(total)
FROM 
(
Select reg, year, total
from com6101 
UNION ALL
Select reg, year, total
from Com6670
)
group by reg, year;




-Dude
Re: total marks (merged) [message #397593 is a reply to message #397288] Sun, 12 April 2009 23:49 Go to previous message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Thanks
Previous Topic: create table
Next Topic: order by month name
Goto Forum:
  


Current Time: Mon Dec 05 14:50:16 CST 2016

Total time taken to generate the page: 0.08639 seconds