
tricky question
Hi all, I have a question, I am not sure if this can be answered in a simple
query or whether it requires pl/sql code, can someone assist?
Table structure looks like:
Student(s) Enrolment(e)
 
stu_no stu_no
name unit_code
degree result
Now I need to make a report showing the average mark in each unit by degree
course, so the report should look something like this:
Unit degree M402 degree S366 degree S367
   
SCC103 54 63 72
SCC205 46 88 56
SCC312 54 72 61
 if anyone has a suggest on how to go about it I would be very appreciative.
Thanks, Karen

Re: tricky question
Kaz,
I see the problem here. Not that it can't be solved using one query, but
I have some doubts. Let me clear first what I get out of your question:
See this query:
select e.unit,
s.deg,
avg(e.result)
from Student s,
Enrolment e
where s.stu_no = e.stu_no
group by
unit_code,
degree
I assume that there is a onetomany (or onetoone) relationship between
"s" and "e". I hope I am right here.
So the above query is capable of giving me a matrix of:
Unit Degree AvgResult
  
U1 D1 44
U1 D2 56
U1 D3 78
U1 D4 24
U2 D1 56
U3 D1 89
U3 D2 44
U3 D3 57
U3 D4 22
(and so on)
Notice that the data is in a vertical form w.r.t the Degrees (D1, D2 etc.)
Whereas you want it to be horizontal. This is called transformation and I
am yet to find a "pure mathematical" derivation of Transformation, where
you w/o knowing the possible values of degree (in this case), can transform
the data vertically/horizontally.
Wherever I have queried this, I got the answer  "it is possible, but you
should know what are the possible key values you will use"
I had been wondering about this. I once got this answer from Kevin Loney
(author of Oracle8i: The Complete Reference) He also pointed the same point.
His book also explains the same point
*For a full example, see "Flipping a Table onto its side", p355 of Oracle8i:
The Complete Reference.*
But let me tell you in advance that if you don't know all the degree courses
in advance, this result can not be formed using single query (at least not
to my knowledge). If you do, its fine and you can transpose the above result
into:
Unit D1 D2 D3 D4
    
U1 44 56 78 24
...
...
and so on
HTH
Cheers
Rohit
"kaz" <karenyoung007@aol.com> wrote:
>
>Hi all, I have a question, I am not sure if this can be answered in a simple
>query or whether it requires pl/sql code, can someone assist?
>
>Table structure looks like:
>Student(s) Enrolment(e)
> 
>stu_no stu_no
>name unit_code
>degree result
>
>Now I need to make a report showing the average mark in each unit by degree
>course, so the report should look something like this:
>
>Unit degree M402 degree S366 degree S367
>   
>SCC103 54 63 72
>SCC205 46 88 56
>SCC312 54 72 61
>
> if anyone has a suggest on how to go about it I would be very appreciative.
>
>Thanks, Karen
>

Re: tricky question
Thanks for the assistance, I do know the degree codes in advance so I will
see how I go, thanks again
Kaz
"Rohit Wason" <rohitw@ggn.hcltech.com> wrote:
>
>Kaz,
>
>I see the problem here. Not that it can't be solved using one query, but
>I have some doubts. Let me clear first what I get out of your question:
>
>See this query:
>
>select e.unit,
> s.deg,
> avg(e.result)
>from Student s,
> Enrolment e
>where s.stu_no = e.stu_no
>group by
> unit_code,
> degree
>
>I assume that there is a onetomany (or onetoone) relationship between
>"s" and "e". I hope I am right here.
>
>So the above query is capable of giving me a matrix of:
>
>Unit Degree AvgResult
>  
>U1 D1 44
>U1 D2 56
>U1 D3 78
>U1 D4 24
>U2 D1 56
>U3 D1 89
>U3 D2 44
>U3 D3 57
>U3 D4 22
>
>(and so on)
>
>Notice that the data is in a vertical form w.r.t the Degrees (D1, D2 etc.)
>Whereas you want it to be horizontal. This is called transformation and
I
>am yet to find a "pure mathematical" derivation of Transformation, where
>you w/o knowing the possible values of degree (in this case), can transform
>the data vertically/horizontally.
>
>Wherever I have queried this, I got the answer  "it is possible, but you
>should know what are the possible key values you will use"
>
>I had been wondering about this. I once got this answer from Kevin Loney
>(author of Oracle8i: The Complete Reference) He also pointed the same point.
>His book also explains the same point
>
>*For a full example, see "Flipping a Table onto its side", p355 of Oracle8i:
>The Complete Reference.*
>
>But let me tell you in advance that if you don't know all the degree courses
>in advance, this result can not be formed using single query (at least not
>to my knowledge). If you do, its fine and you can transpose the above result
>into:
>
>Unit D1 D2 D3 D4
>    
>U1 44 56 78 24
>...
>...
>and so on
>
>HTH
>Cheers
>Rohit
>
>
>
>
>
>"kaz" <karenyoung007@aol.com> wrote:
>>
>>Hi all, I have a question, I am not sure if this can be answered in a simple
>>query or whether it requires pl/sql code, can someone assist?
>>
>>Table structure looks like:
>>Student(s) Enrolment(e)
>> 
>>stu_no stu_no
>>name unit_code
>>degree result
>>
>>Now I need to make a report showing the average mark in each unit by degree
>>course, so the report should look something like this:
>>
>>Unit degree M402 degree S366 degree S367
>>   
>>SCC103 54 63 72
>>SCC205 46 88 56
>>SCC312 54 72 61
>>
>> if anyone has a suggest on how to go about it I would be very appreciative.
>>
>>Thanks, Karen
>>
>

Re: tricky question
Hi Kaz,
As Rohit's queries, I add some more for you.
select e.unit_code unit,
avg(decode(s.degree,M402',e.result,0) degree_M402,
avg(decode(s.degree,S366',e.result,0) degree_S366,
avg(decode(s.degree,S367',e.result,0) degree_S367,
.......... (the advance)...............
from Student s,
Enrolment e
where s.stu_no = e.stu_no
group by e.unit_code
psit
"kaz" <karenyoung007@aol.com> wrote:
>
>Thanks for the assistance, I do know the degree codes in advance so I will
>see how I go, thanks again
>
>Kaz
>
>
>"Rohit Wason" <rohitw@ggn.hcltech.com> wrote:
>>
>>Kaz,
>>
>>I see the problem here. Not that it can't be solved using one query, but
>>I have some doubts. Let me clear first what I get out of your question:
>>
>>See this query:
>>
>>select e.unit,
>> s.deg,
>> avg(e.result)
>>from Student s,
>> Enrolment e
>>where s.stu_no = e.stu_no
>>group by
>> unit_code,
>> degree
>>
>>I assume that there is a onetomany (or onetoone) relationship between
>>"s" and "e". I hope I am right here.
>>
>>So the above query is capable of giving me a matrix of:
>>
>>Unit Degree AvgResult
>>  
>>U1 D1 44
>>U1 D2 56
>>U1 D3 78
>>U1 D4 24
>>U2 D1 56
>>U3 D1 89
>>U3 D2 44
>>U3 D3 57
>>U3 D4 22
>>
>>(and so on)
>>
>>Notice that the data is in a vertical form w.r.t the Degrees (D1, D2 etc.)
>>Whereas you want it to be horizontal. This is called transformation and
>I
>>am yet to find a "pure mathematical" derivation of Transformation, where
>>you w/o knowing the possible values of degree (in this case), can transform
>>the data vertically/horizontally.
>>
>>Wherever I have queried this, I got the answer  "it is possible, but you
>>should know what are the possible key values you will use"
>>
>>I had been wondering about this. I once got this answer from Kevin Loney
>>(author of Oracle8i: The Complete Reference) He also pointed the same point.
>>His book also explains the same point
>>
>>*For a full example, see "Flipping a Table onto its side", p355 of Oracle8i:
>>The Complete Reference.*
>>
>>But let me tell you in advance that if you don't know all the degree courses
>>in advance, this result can not be formed using single query (at least
not
>>to my knowledge). If you do, its fine and you can transpose the above result
>>into:
>>
>>Unit D1 D2 D3 D4
>>    
>>U1 44 56 78 24
>>...
>>...
>>and so on
>>
>>HTH
>>Cheers
>>Rohit
>>
>>
>>
>>
>>
>>"kaz" <karenyoung007@aol.com> wrote:
>>>
>>>Hi all, I have a question, I am not sure if this can be answered in a
simple
>>>query or whether it requires pl/sql code, can someone assist?
>>>
>>>Table structure looks like:
>>>Student(s) Enrolment(e)
>>> 
>>>stu_no stu_no
>>>name unit_code
>>>degree result
>>>
>>>Now I need to make a report showing the average mark in each unit by degree
>>>course, so the report should look something like this:
>>>
>>>Unit degree M402 degree S366 degree S367
>>>   
>>>SCC103 54 63 72
>>>SCC205 46 88 56
>>>SCC312 54 72 61
>>>
>>> if anyone has a suggest on how to go about it I would be very appreciative.
>>>
>>>Thanks, Karen
>>>
>>
>

Re: tricky question
Thank you very much, ,psit, you have saved me a lot of trouble and grief after
trying to write pl/sql code for several hours to answer my problem, it seems
that you are spot on with the decode function. Thanks again
Karen
"psit" <psit@chula.com> wrote:
>
>Hi Kaz,
> As Rohit's queries, I add some more for you.
>select e.unit_code unit,
> avg(decode(s.degree,M402',e.result,0) degree_M402,
> avg(decode(s.degree,S366',e.result,0) degree_S366,
> avg(decode(s.degree,S367',e.result,0) degree_S367,
> .......... (the advance)...............
>from Student s,
> Enrolment e
>where s.stu_no = e.stu_no
>group by e.unit_code
>
>psit
>
>"kaz" <karenyoung007@aol.com> wrote:
>>
>>Thanks for the assistance, I do know the degree codes in advance so I will
>>see how I go, thanks again
>>
>>Kaz
>>
>>
>>"Rohit Wason" <rohitw@ggn.hcltech.com> wrote:
>>>
>>>Kaz,
>>>
>>>I see the problem here. Not that it can't be solved using one query, but
>>>I have some doubts. Let me clear first what I get out of your question:
>>>
>>>See this query:
>>>
>>>select e.unit,
>>> s.deg,
>>> avg(e.result)
>>>from Student s,
>>> Enrolment e
>>>where s.stu_no = e.stu_no
>>>group by
>>> unit_code,
>>> degree
>>>
>>>I assume that there is a onetomany (or onetoone) relationship between
>>>"s" and "e". I hope I am right here.
>>>
>>>So the above query is capable of giving me a matrix of:
>>>
>>>Unit Degree AvgResult
>>>  
>>>U1 D1 44
>>>U1 D2 56
>>>U1 D3 78
>>>U1 D4 24
>>>U2 D1 56
>>>U3 D1 89
>>>U3 D2 44
>>>U3 D3 57
>>>U3 D4 22
>>>
>>>(and so on)
>>>
>>>Notice that the data is in a vertical form w.r.t the Degrees (D1, D2 etc.)
>>>Whereas you want it to be horizontal. This is called transformation and
>>I
>>>am yet to find a "pure mathematical" derivation of Transformation, where
>>>you w/o knowing the possible values of degree (in this case), can transform
>>>the data vertically/horizontally.
>>>
>>>Wherever I have queried this, I got the answer  "it is possible, but
you
>>>should know what are the possible key values you will use"
>>>
>>>I had been wondering about this. I once got this answer from Kevin Loney
>>>(author of Oracle8i: The Complete Reference) He also pointed the same
point.
>>>His book also explains the same point
>>>
>>>*For a full example, see "Flipping a Table onto its side", p355 of Oracle8i:
>>>The Complete Reference.*
>>>
>>>But let me tell you in advance that if you don't know all the degree courses
>>>in advance, this result can not be formed using single query (at least
>not
>>>to my knowledge). If you do, its fine and you can transpose the above
result
>>>into:
>>>
>>>Unit D1 D2 D3 D4
>>>    
>>>U1 44 56 78 24
>>>...
>>>...
>>>and so on
>>>
>>>HTH
>>>Cheers
>>>Rohit
>>>
>>>
>>>
>>>
>>>
>>>"kaz" <karenyoung007@aol.com> wrote:
>>>>
>>>>Hi all, I have a question, I am not sure if this can be answered in a
>simple
>>>>query or whether it requires pl/sql code, can someone assist?
>>>>
>>>>Table structure looks like:
>>>>Student(s) Enrolment(e)
>>>> 
>>>>stu_no stu_no
>>>>name unit_code
>>>>degree result
>>>>
>>>>Now I need to make a report showing the average mark in each unit by
degree
>>>>course, so the report should look something like this:
>>>>
>>>>Unit degree M402 degree S366 degree S367
>>>>   
>>>>SCC103 54 63 72
>>>>SCC205 46 88 56
>>>>SCC312 54 72 61
>>>>
>>>> if anyone has a suggest on how to go about it I would be very appreciative.
>>>>
>>>>Thanks, Karen
>>>>
>>>
>>
>

Re: tricky question
You're welcome, Karen, I am an old Oracle version user, it take me a lot of
time to get the power of them without PL/SQL. ANd the decode function is
one of the several poerful Oracle function.
psit
"kaz" <karenyoung007@aol.com> wrote:
>
>Thank you very much, ,psit, you have saved me a lot of trouble and grief
after
>trying to write pl/sql code for several hours to answer my problem, it seems
>that you are spot on with the decode function. Thanks again
> Karen
>
>
>
>"psit" <psit@chula.com> wrote:
>>
>>Hi Kaz,
>> As Rohit's queries, I add some more for you.
>>select e.unit_code unit,
>> avg(decode(s.degree,M402',e.result,0) degree_M402,
>> avg(decode(s.degree,S366',e.result,0) degree_S366,
>> avg(decode(s.degree,S367',e.result,0) degree_S367,
>> .......... (the advance)...............
>>from Student s,
>> Enrolment e
>>where s.stu_no = e.stu_no
>>group by e.unit_code
>>
>>psit
>>
>>"kaz" <karenyoung007@aol.com> wrote:
>>>
>>>Thanks for the assistance, I do know the degree codes in advance so I
will
>>>see how I go, thanks again
>>>
>>>Kaz
>>>
>>>
>>>"Rohit Wason" <rohitw@ggn.hcltech.com> wrote:
>>>>
>>>>Kaz,
>>>>
>>>>I see the problem here. Not that it can't be solved using one query,
but
>>>>I have some doubts. Let me clear first what I get out of your question:
>>>>
>>>>See this query:
>>>>
>>>>select e.unit,
>>>> s.deg,
>>>> avg(e.result)
>>>>from Student s,
>>>> Enrolment e
>>>>where s.stu_no = e.stu_no
>>>>group by
>>>> unit_code,
>>>> degree
>>>>
>>>>I assume that there is a onetomany (or onetoone) relationship between
>>>>"s" and "e". I hope I am right here.
>>>>
>>>>So the above query is capable of giving me a matrix of:
>>>>
>>>>Unit Degree AvgResult
>>>>  
>>>>U1 D1 44
>>>>U1 D2 56
>>>>U1 D3 78
>>>>U1 D4 24
>>>>U2 D1 56
>>>>U3 D1 89
>>>>U3 D2 44
>>>>U3 D3 57
>>>>U3 D4 22
>>>>
>>>>(and so on)
>>>>
>>>>Notice that the data is in a vertical form w.r.t the Degrees (D1, D2
etc.)
>>>>Whereas you want it to be horizontal. This is called transformation and
>>>I
>>>>am yet to find a "pure mathematical" derivation of Transformation, where
>>>>you w/o knowing the possible values of degree (in this case), can transform
>>>>the data vertically/horizontally.
>>>>
>>>>Wherever I have queried this, I got the answer  "it is possible, but
>you
>>>>should know what are the possible key values you will use"
>>>>
>>>>I had been wondering about this. I once got this answer from Kevin Loney
>>>>(author of Oracle8i: The Complete Reference) He also pointed the same
>point.
>>>>His book also explains the same point
>>>>
>>>>*For a full example, see "Flipping a Table onto its side", p355 of Oracle8i:
>>>>The Complete Reference.*
>>>>
>>>>But let me tell you in advance that if you don't know all the degree
courses
>>>>in advance, this result can not be formed using single query (at least
>>not
>>>>to my knowledge). If you do, its fine and you can transpose the above
>result
>>>>into:
>>>>
>>>>Unit D1 D2 D3 D4
>>>>    
>>>>U1 44 56 78 24
>>>>...
>>>>...
>>>>and so on
>>>>
>>>>HTH
>>>>Cheers
>>>>Rohit
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>"kaz" <karenyoung007@aol.com> wrote:
>>>>>
>>>>>Hi all, I have a question, I am not sure if this can be answered in
a
>>simple
>>>>>query or whether it requires pl/sql code, can someone assist?
>>>>>
>>>>>Table structure looks like:
>>>>>Student(s) Enrolment(e)
>>>>> 
>>>>>stu_no stu_no
>>>>>name unit_code
>>>>>degree result
>>>>>
>>>>>Now I need to make a report showing the average mark in each unit by
>degree
>>>>>course, so the report should look something like this:
>>>>>
>>>>>Unit degree M402 degree S366 degree S367
>>>>>   
>>>>>SCC103 54 63 72
>>>>>SCC205 46 88 56
>>>>>SCC312 54 72 61
>>>>>
>>>>> if anyone has a suggest on how to go about it I would be very appreciative.
>>>>>
>>>>>Thanks, Karen
>>>>>
>>>>
>>>
>>
>
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

Development Centers
 Android Development Center
 Cloud Development Project Center
 HTML5 Development Center
 Windows Mobile Development Center
