techprocess
asked on
Rollup/cube query Oracle 10g/9i. One final help needed......Plzzzzzzzzz help this poor soul
As Sujith and Paquicuba have been answering my questions, I needed to modify the query to suit my own one. However when I did so, I didn't get the desired results. The reason was the structure of the query I needed was something different. Here's a model of what I want in my query.
Taking the same example of student marks.
ID SEMESTER SUBJECT_TYPE SUBJECT THEORY_TOT THEORY_OBT PRAC_TOT PRAC_OBT MARKS
9 4 MAIN Maths 50 42 50 39 81
9 4 SECONDARY English 50 40 40
9 4 SECONDARY Computers 50 48 50 45 93
10 5 MAIN Maths 50 45 50 40 85
10 5 SECONDARY History 50 43 43
11 4 MAIN Maths 50 42 50 39 81
11 4 SECONDARY English 50 40 40
11 4 SECONDARY Computers 50 48 50 45 93
That's the sample table(please forgive the formatting. I've done it properly. Don't know why this textbox doesn't take it as it is).
And This is the desired output
ID SEM SUBJECT_TYPE SUBJECT THEORY_TOT THEORY_OBT PRAC_TOT PRAC_OBT MARKS
9 4 MAIN Maths 50 42 50 39 81
Total 81
9 4 SECONDARY English 50 40 40
9 4 SECONDARY Computers 50 48 50 45 93
Total 133
10 5 MAIN Maths 50 45 50 40 85
Total 85
10 5 SECONDARY History 50 43 43
Total 43
11 4 MAIN Maths 50 42 50 39 81
Total 81
11 4 SECONDARY English 50 40 40
11 4 SECONDARY Computers 50 48 50 45 93
Total 133
And then a rank Column to be added with the person with rank 1 at the top like this....
FINAL OUTPUT:
ID SEM SUBJECT_TYPE SUBJECT THEORY_TOT THEORY_OBT PRAC_TOT PRAC_OBT MARKS RANK
9 4 SECONDARY English 50 40 40
9 4 SECONDARY Computers 50 48 50 45 93
Total 133 1
11 4 SECONDARY English 50 40 40
11 4 SECONDARY Computers 50 48 50 45 93
Total 133 1
10 5 MAIN Maths 50 45 50 40 85
Total 85 2
9 4 MAIN Maths 50 42 50 39 81
Total 81 3
11 4 MAIN Maths 50 42 50 39 81
Total 81 3
10 5 SECONDARY History 50 43 43
Total 43 4
Please don't go about the logics about the model I've posted. The result that I've wanted for the past 1 week is somewhat like this.
Can anybody please help me out.
I would be very highly grateful to u guys @experts exchange.
I would gladly give a 1000points if its available.
Really this question is getting to me now guys.. Plz help....
Taking the same example of student marks.
ID SEMESTER SUBJECT_TYPE SUBJECT THEORY_TOT THEORY_OBT PRAC_TOT PRAC_OBT MARKS
9 4 MAIN Maths 50 42 50 39 81
9 4 SECONDARY English 50 40 40
9 4 SECONDARY Computers 50 48 50 45 93
10 5 MAIN Maths 50 45 50 40 85
10 5 SECONDARY History 50 43 43
11 4 MAIN Maths 50 42 50 39 81
11 4 SECONDARY English 50 40 40
11 4 SECONDARY Computers 50 48 50 45 93
That's the sample table(please forgive the formatting. I've done it properly. Don't know why this textbox doesn't take it as it is).
And This is the desired output
ID SEM SUBJECT_TYPE SUBJECT THEORY_TOT THEORY_OBT PRAC_TOT PRAC_OBT MARKS
9 4 MAIN Maths 50 42 50 39 81
Total 81
9 4 SECONDARY English 50 40 40
9 4 SECONDARY Computers 50 48 50 45 93
Total 133
10 5 MAIN Maths 50 45 50 40 85
Total 85
10 5 SECONDARY History 50 43 43
Total 43
11 4 MAIN Maths 50 42 50 39 81
Total 81
11 4 SECONDARY English 50 40 40
11 4 SECONDARY Computers 50 48 50 45 93
Total 133
And then a rank Column to be added with the person with rank 1 at the top like this....
FINAL OUTPUT:
ID SEM SUBJECT_TYPE SUBJECT THEORY_TOT THEORY_OBT PRAC_TOT PRAC_OBT MARKS RANK
9 4 SECONDARY English 50 40 40
9 4 SECONDARY Computers 50 48 50 45 93
Total 133 1
11 4 SECONDARY English 50 40 40
11 4 SECONDARY Computers 50 48 50 45 93
Total 133 1
10 5 MAIN Maths 50 45 50 40 85
Total 85 2
9 4 MAIN Maths 50 42 50 39 81
Total 81 3
11 4 MAIN Maths 50 42 50 39 81
Total 81 3
10 5 SECONDARY History 50 43 43
Total 43 4
Please don't go about the logics about the model I've posted. The result that I've wanted for the past 1 week is somewhat like this.
Can anybody please help me out.
I would be very highly grateful to u guys @experts exchange.
I would gladly give a 1000points if its available.
Really this question is getting to me now guys.. Plz help....
Sujith
Provide the table and insert scripts for the sample data.
ASKER
create table statement:
CREATE TABLE TMP_SP_MARKS
(
STUDID NUMBER(2),
SEMESTER NUMBER(2),
SUBJECT_TYPE VARCHAR2(15),
THEORY_TOT NUMBER(5, 2),
THEORY_OBT NUMBER(5, 2),
PRACT_TOT NUMBER(5, 2),
PRACT_OBT NUMBER(5, 2),
MARKS NUMBER(5, 2)
)
;
CREATE TABLE TMP_SP_MARKS
(
STUDID NUMBER(2),
SEMESTER NUMBER(2),
SUBJECT_TYPE VARCHAR2(15),
THEORY_TOT NUMBER(5, 2),
THEORY_OBT NUMBER(5, 2),
PRACT_TOT NUMBER(5, 2),
PRACT_OBT NUMBER(5, 2),
MARKS NUMBER(5, 2)
)
;
ASKER
create table statement:
CREATE TABLE TMP_SP_MARKS
(
STUDID NUMBER(2),
SEMESTER NUMBER(2),
SUBJECT_TYPE VARCHAR2(15),
THEORY_TOT NUMBER(5, 2),
THEORY_OBT NUMBER(5, 2),
PRACT_TOT NUMBER(5, 2),
PRACT_OBT NUMBER(5, 2),
MARKS NUMBER(5, 2)
)
;
CREATE TABLE TMP_SP_MARKS
(
STUDID NUMBER(2),
SEMESTER NUMBER(2),
SUBJECT_TYPE VARCHAR2(15),
THEORY_TOT NUMBER(5, 2),
THEORY_OBT NUMBER(5, 2),
PRACT_TOT NUMBER(5, 2),
PRACT_OBT NUMBER(5, 2),
MARKS NUMBER(5, 2)
)
;
ASKER
insert into tmp_sp_marks
values(9,4,'MAIN','Maths',
insert into tmp_sp_marks
values(9,4,'SECONDARY','En
insert into tmp_sp_marks
values(9,4,'SECONDARY','Co
insert into tmp_sp_marks
values(10,5,'MAIN','Maths'
insert into tmp_sp_marks
values(10,5,'SECONDARY','H
insert into tmp_sp_marks
values(11,4,'MAIN','Maths'
insert into tmp_sp_marks
values(11,4,'SECONDARY','E
insert into tmp_sp_marks
values(11,4,'SECONDARY','C
values(9,4,'MAIN','Maths',
insert into tmp_sp_marks
values(9,4,'SECONDARY','En
insert into tmp_sp_marks
values(9,4,'SECONDARY','Co
insert into tmp_sp_marks
values(10,5,'MAIN','Maths'
insert into tmp_sp_marks
values(10,5,'SECONDARY','H
insert into tmp_sp_marks
values(11,4,'MAIN','Maths'
insert into tmp_sp_marks
values(11,4,'SECONDARY','E
insert into tmp_sp_marks
values(11,4,'SECONDARY','C
ASKER
insert into tmp_sp_marks
values(9,4,'MAIN','Maths',
insert into tmp_sp_marks
values(9,4,'SECONDARY','En
insert into tmp_sp_marks
values(9,4,'SECONDARY','Co
insert into tmp_sp_marks
values(10,5,'MAIN','Maths'
insert into tmp_sp_marks
values(10,5,'SECONDARY','H
insert into tmp_sp_marks
values(11,4,'MAIN','Maths'
insert into tmp_sp_marks
values(11,4,'SECONDARY','E
insert into tmp_sp_marks
values(11,4,'SECONDARY','C
values(9,4,'MAIN','Maths',
insert into tmp_sp_marks
values(9,4,'SECONDARY','En
insert into tmp_sp_marks
values(9,4,'SECONDARY','Co
insert into tmp_sp_marks
values(10,5,'MAIN','Maths'
insert into tmp_sp_marks
values(10,5,'SECONDARY','H
insert into tmp_sp_marks
values(11,4,'MAIN','Maths'
insert into tmp_sp_marks
values(11,4,'SECONDARY','E
insert into tmp_sp_marks
values(11,4,'SECONDARY','C
ASKER
CREATE TABLE TMP_SP_MARKS
(
STUDID NUMBER(2),
SEMESTER NUMBER(2),
SUBJECT_TYPE VARCHAR2(15),
SUBJECT VARCHAR2(15),
THEORY_TOT NUMBER(5, 2),
THEORY_OBT NUMBER(5, 2),
PRACT_TOT NUMBER(5, 2),
PRACT_OBT NUMBER(5, 2),
MARKS NUMBER(5, 2)
)
;
insert into tmp_sp_marks
values(9,4,'MAIN','Maths',
insert into tmp_sp_marks
values(9,4,'SECONDARY','En
insert into tmp_sp_marks
values(9,4,'SECONDARY','Co
insert into tmp_sp_marks
values(10,5,'MAIN','Maths'
insert into tmp_sp_marks
values(10,5,'SECONDARY','H
insert into tmp_sp_marks
values(11,4,'MAIN','Maths'
insert into tmp_sp_marks
values(11,4,'SECONDARY','E
insert into tmp_sp_marks
values(11,4,'SECONDARY','C
(
STUDID NUMBER(2),
SEMESTER NUMBER(2),
SUBJECT_TYPE VARCHAR2(15),
SUBJECT VARCHAR2(15),
THEORY_TOT NUMBER(5, 2),
THEORY_OBT NUMBER(5, 2),
PRACT_TOT NUMBER(5, 2),
PRACT_OBT NUMBER(5, 2),
MARKS NUMBER(5, 2)
)
;
insert into tmp_sp_marks
values(9,4,'MAIN','Maths',
insert into tmp_sp_marks
values(9,4,'SECONDARY','En
insert into tmp_sp_marks
values(9,4,'SECONDARY','Co
insert into tmp_sp_marks
values(10,5,'MAIN','Maths'
insert into tmp_sp_marks
values(10,5,'SECONDARY','H
insert into tmp_sp_marks
values(11,4,'MAIN','Maths'
insert into tmp_sp_marks
values(11,4,'SECONDARY','E
insert into tmp_sp_marks
values(11,4,'SECONDARY','C
This should do the job.
select
studid, semester, subject_type, subject, theory_tot, theory_obt, pract_tot, pract_obt, marks,
case when subject is null then marks else 0 end total_marks,
dense_rank() over (partition by null order by case when subject is null then marks else 0 end desc) rk
from
(
select
studid,
semester, subject_type, subject,
sum(theory_tot) theory_tot,
sum(theory_obt) theory_obt,
sum(pract_tot) pract_tot,
sum(pract_obt) pract_obt,
sum(marks) marks
from TMP_SP_MARKS
group by grouping sets ((studid, semester, subject_type, subject),(studid, semester, subject_type))
)
select
studid, semester, subject_type, subject, theory_tot, theory_obt, pract_tot, pract_obt, marks,
case when subject is null then marks else 0 end total_marks,
dense_rank() over (partition by null order by case when subject is null then marks else 0 end desc) rk
from
(
select
studid,
semester, subject_type, subject,
sum(theory_tot) theory_tot,
sum(theory_obt) theory_obt,
sum(pract_tot) pract_tot,
sum(pract_obt) pract_obt,
sum(marks) marks
from TMP_SP_MARKS
group by grouping sets ((studid, semester, subject_type, subject),(studid, semester, subject_type))
)
Ignore this line
case when subject is null then marks else 0 end total_marks
case when subject is null then marks else 0 end total_marks
ASKER
Can u show me the output of this query on ur machine
ASKER
I want my output something like that in this excel file
http://k.domaindlx.com/iamanidiot/requiredoutput.xls
http://k.domaindlx.com/iamanidiot/requiredoutput.xls
Does it need to be in rank order with the scores for the best student first?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.