Home » SQL & PL/SQL » SQL & PL/SQL » Query with Row Generator
Query with Row Generator [message #376513] Wed, 17 December 2008 12:04 Go to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
I'm just looking for some suggestions on how to handle writing this query (row_number, group by, group_id...etc).

CREATE TABLE RESULTS
(
  STRM               VARCHAR2(4 BYTE),
  CLASS_NBR          INTEGER,
  INSTRUCTOR_EMPLID  VARCHAR2(11 BYTE),
  RATING_ID          NUMBER
)

CREATE TABLE RATING
(
  ID             NUMBER,
  RATING         NUMBER
)

Table Results:

"3643", "111", "99", "5"
"3643", "111", "99", "5"
"3643", "111", "99", "4"
"3643", "111", "99", "4"
"3643", "111", "99", "3"
"3643", "111", "99", "2"

"3643", "112", "98", "5"
"3643", "112", "98", "5"
"3643", "112", "98", "4"
"3643", "112", "98", "4"
"3643", "112", "98", "3"
"3643", "112", "98", "2"

Table Rating:
"0", "0"
"1", "1"
"2", "2"
"3", "3"
"4", "4"
"5", "5"

Output should look like this:

INSTRUCTOR_EMPLID CLASS_NBR RATING5 RATING4 RATING3 RATING2 RATING1 RATING0

99   111  2  2 1 1 0 0 0
98   112  2  2 1 1 0 0 0


So, for each instructor for each class, I need the count of the 5 responses, 4 responses, 3 responses, 2 responses, 1 responses and 0 responses on a single row.

I've come up with this but it doesn't give me a count of the individual responses.

select b.strm, 
       b.instructor_emplid,
       b.class_nbr, 
       max(decode(a.ratingNumber, 05, a.rating)) rating5,
       max(decode(a.ratingNumber, 04, a.rating)) rating4,
       max(decode(a.ratingNumber, 03, a.rating)) rating3,
       max(decode(a.ratingNumber, 02, a.rating)) rating2,
       max(decode(a.ratingNumber, 01, a.rating)) rating1,
       max(decode(a.ratingNumber, 00, a.rating)) rating0
  from (select rating,
               row_number() over (order by rating) ratingNumber
          from rating) a,
       results         b
    where a.rating = b.rating_id(+)
      group by b.strm, 
               b.instructor_emplid,
               b.class_nbr
        order by b.instructor_emplid
Re: Query with Row Generator [message #376515 is a reply to message #376513] Wed, 17 December 2008 12:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What result do you get?
What about just counting?

Post insert statements for the test case.

Regards
Michel
Re: Query with Row Generator [message #376518 is a reply to message #376513] Wed, 17 December 2008 12:26 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
What I got was just the response for each column if there was one.

Like:

INSTRUCTOR_EMPLID CLASS_NBR RATING5 RATING4 RATING3 RATING2 RATING1 RATING0

99 111 5 4 3 2 0 0 0
98 112 5 4 3 2 0 0 0

That just told me that someone entered a rating of 5 but didn't tell me how many.

Adding count just gives me a "not a group by" error.
Re: Query with Row Generator [message #376524 is a reply to message #376518] Wed, 17 December 2008 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format your output, align the columns, post insert statements.

Regards
Michel
Re: Query with Row Generator [message #376529 is a reply to message #376513] Wed, 17 December 2008 13:00 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Insert statements:
INSERT INTO results VALUES ('3643', '111', '99', '5');
INSERT INTO results VALUES ('3643', '111', '99', '5');
INSERT INTO results VALUES ('3643', '111', '99', '4');
INSERT INTO results VALUES ('3643', '111', '99', '3');
INSERT INTO results VALUES ('3643', '111', '99', '3');
INSERT INTO results VALUES ('3643', '111', '99', '2');
INSERT INTO results VALUES ('3643', '111', '99', '2');
INSERT INTO results VALUES ('3643', '111', '99', '2');

INSERT INTO results VALUES ('3643', '111', '98', '5');
INSERT INTO results VALUES ('3643', '111', '98', '5');
INSERT INTO results VALUES ('3643', '111', '98', '4');
INSERT INTO results VALUES ('3643', '111', '98', '3');
INSERT INTO results VALUES ('3643', '111', '98', '3');
INSERT INTO results VALUES ('3643', '111', '98', '2');
INSERT INTO results VALUES ('3643', '111', '98', '2');
INSERT INTO results VALUES ('3643', '111', '98', '2');

INSERT INTO rating VALUES (0, 0);
INSERT INTO rating VALUES (1, 1);
INSERT INTO rating VALUES (2, 2);
INSERT INTO rating VALUES (3, 3);
INSERT INTO rating VALUES (4, 4);
INSERT INTO rating VALUES (5, 5);

The output should be:

Instr    CLASS   R5  R4  R3  R2  R1  R0
99        111     2   1   2   3   0   0
98        112     2   1   2   3   0   0 

Re: Query with Row Generator [message #376540 is a reply to message #376529] Wed, 17 December 2008 14:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With just an outer join and a count instead of max (maybe on another column) you will get the result.

Regards
Michel

[Updated on: Wed, 17 December 2008 14:03]

Report message to a moderator

Re: Query with Row Generator [message #376549 is a reply to message #376513] Wed, 17 December 2008 15:25 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Using the query I posted, I don't see it and/or don't see how you are doing it.

Never mind, I figured it out. Remove the MAX and replace with COUNT.

[Updated on: Wed, 17 December 2008 15:29]

Report message to a moderator

Re: Query with Row Generator [message #376588 is a reply to message #376549] Wed, 17 December 2008 23:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a good, fair and appreciate practice to post the solution you found.
Then it can benefit to others and we can help you to improve it.

Here's what I meant:
SQL> select s.INSTRUCTOR_EMPLID, s.CLASS_NBR,
  2         nvl(count(decode(s.RATING_ID,5,1)),0) r5,
  3         nvl(count(decode(s.RATING_ID,4,1)),0) r4,
  4         nvl(count(decode(s.RATING_ID,3,1)),0) r3,
  5         nvl(count(decode(s.RATING_ID,2,1)),0) r2,
  6         nvl(count(decode(s.RATING_ID,1,1)),0) r1,
  7         nvl(count(decode(s.RATING_ID,0,1)),0) r0
  8  from rating r, results s
  9  where s.RATING_ID (+) = r.ID 
 10    and s.INSTRUCTOR_EMPLID is not null
 11  group by s.INSTRUCTOR_EMPLID, s.CLASS_NBR
 12  order by s.CLASS_NBR
 13  /
INSTRUCTOR_  CLASS_NBR         R5         R4         R3         R2         R1         R0
----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
98                 111          2          1          2          3          0          0
99                 111          2          1          2          3          0          0

2 rows selected.

Regards
Michel
Re: Query with Row Generator [message #376758 is a reply to message #376513] Thu, 18 December 2008 08:40 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Ah, sorry about that. I just took my original query and replaced the MAX with COUNT. Now that I see yours, I wonder why I didn't just do that. It looks like I made it harder than it was.

select b.strm, 
       b.instructor_emplid,
       b.class_nbr, 
       count(decode(a.ratingNumber, 6, a.rating)) rating5,
       count(decode(a.ratingNumber, 5, a.rating)) rating4,
       count(decode(a.ratingNumber, 4, a.rating)) rating3,
       count(decode(a.ratingNumber, 3, a.rating)) rating2,
       count(decode(a.ratingNumber, 2, a.rating)) rating1,
       count(decode(a.ratingNumber, 1, a.rating)) rating0
  from (select rating,
               row_number() over (order by rating) ratingNumber
          from rating) a,
       results         b
    where a.rating = b.rating_id(+)
      group by b.strm, 
               b.instructor_emplid,
               b.class_nbr
        order by b.instructor_emplid

Re: Query with Row Generator [message #376778 is a reply to message #376588] Thu, 18 December 2008 09:30 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Michael,

Just out of curiosity why do you have a nvl around count function ?

Regards

Raj
Re: Query with Row Generator [message #376779 is a reply to message #376513] Thu, 18 December 2008 09:35 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
I can answer this one. So that it returns a zero if the count for that response (1 to 5) is null.

select NVL(commission, 0)
  from sales;

This SQL statement would return 0 if the commission field contained a null value. Otherwise, it would return the commission field.

[Updated on: Fri, 19 December 2008 00:51] by Moderator

Report message to a moderator

Re: Query with Row Generator [message #376784 is a reply to message #376779] Thu, 18 December 2008 09:43 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

why do you have a nvl around count function ?


I think you didn't read my post properly.

Regards

Raj

Re: Query with Row Generator [message #376789 is a reply to message #376513] Thu, 18 December 2008 09:51 Go to previous messageGo to next message
Duane
Messages: 452
Registered: December 2002
Senior Member
Ah, I think what you are saying is that, count will return zero (0) if nothing is counted, therefore, you don't need the NVL function around count.

Running my query backs up that assumption.
Re: Query with Row Generator [message #376886 is a reply to message #376778] Fri, 19 December 2008 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
S.Rajaram wrote on Thu, 18 December 2008 16:30
Michael,

Just out of curiosity why do you have a nvl around count function ?

Regards

Raj

It is useless here, it is just an habit for me when there is an outer join to automatically put nvl and then to think if it is useless.
I didn't do the second step here.

Regards
Michel

Re: Query with Row Generator [message #376898 is a reply to message #376886] Fri, 19 December 2008 01:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Now we're at it: looks to me like the whole outer join is something that's leftover from a previous approach.
Considering the construction in line 10, there will never be a join with a non-existing row in s.

(Still talking about Michels query)

[Edit: Unless, of course, I am horribly mistaking.. As Maaher used to say: still need my morning coffee Wink]

[Updated on: Fri, 19 December 2008 01:17]

Report message to a moderator

Re: Query with Row Generator [message #376901 is a reply to message #376898] Fri, 19 December 2008 01:21 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Laughing
Of course you are right, outer join is also useless (and misused) here.
SQL> select s.INSTRUCTOR_EMPLID, s.CLASS_NBR,
  2         count(decode(s.RATING_ID,5,1)) r5,
  3         count(decode(s.RATING_ID,4,1)) r4,
  4         count(decode(s.RATING_ID,3,1)) r3,
  5         count(decode(s.RATING_ID,2,1)) r2,
  6         count(decode(s.RATING_ID,1,1)) r1,
  7         count(decode(s.RATING_ID,0,1)) r0
  8  from rating r, results s
  9  where s.RATING_ID = r.ID 
 10  group by s.INSTRUCTOR_EMPLID, s.CLASS_NBR
 11  order by s.CLASS_NBR
 12  /
INSTRUCTOR_  CLASS_NBR         R5         R4         R3         R2         R1         R0
----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
98                 111          2          1          2          3          0          0
99                 111          2          1          2          3          0          0

2 rows selected.

Regards
Michel
Previous Topic: help in String manipulation
Next Topic: utl_file
Goto Forum:
  


Current Time: Mon Dec 05 15:08:09 CST 2016

Total time taken to generate the page: 0.10222 seconds