This discussion is archived
7 Replies Latest reply: Dec 9, 2012 9:13 PM by sb92075 RSS

with clause query with self join

Maahjoor Newbie
Currently Being Moderated
dear all ,

i have the following table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 SURVEY_ID                                 NOT NULL NUMBER(5)
 USER_ID                                   NOT NULL VARCHAR2(15)
 ACADEMIC_SEMESTER                                  VARCHAR2(25)
 USER_TYPE                                          VARCHAR2(7)
 Q1                                            NUMBER(1)
 Q2                                            NUMBER(1)
Q1 and Q2 both have five questions 1,2,3,4,5.

i want to see how much user has slected option 1, how much user select option2 so on and so forth for each question.

how to do this?

i write the following query

with
Q1_count as (
select Q1_STAFF,Q13_STAFF,SURVEY_ID,user_id from css_survey
)
select count(c.user_id) total_users ,q.Q1_STAFF,q.Q13_STAFF
from css_survey c,q1_count q
where c.survey_id=q.SURVEY_ID and c.user_id=q.user_id
group by q.Q1_STAFF,q.Q13_STAFF

which generates the following results
TOTAL_USERS     Q1         Q2
----------- ----------             ----------
          1              2               5
          1              5               5
instead i need the below results;
TOTAL_USERS     Q1         Q2
----------- ----------             ----------
          1              2               
          1              5               
          1                            5
          1                            5
kindly help.
  • 1. Re: with clause query with self join
    jeneesh Guru
    Currently Being Moderated
    I do remember, this qustion was asked before

    And solution was aslo given at that time
    select count (*) total,q1,q2
    from css_survey
    group by  grouping sets (q1,q2);
  • 2. Re: with clause query with self join
    Maahjoor Newbie
    Currently Being Moderated
    Yes, ofcouse, i actually post that message, and i forgot.

    thank you so much, the question is answered.
  • 3. Re: with clause query with self join
    Maahjoor Newbie
    Currently Being Moderated
    i am sorry, i mark the question as a correct answer but there is a problem in the query.

    the table have the data i mentioned before.

    when i insert one more record using the query blow,
    insert into css_survey(user_id,survey_id,user_type,academic_semester,q2) values
    ('et04',4,'Staff','Semester1(2012-2013)',5)
    the query return the following results.
    SQL>  select count (survey_id) total,q1_staff,q13_staff
      2  from css_survey
      3* group by  grouping sets (q1_staff,q13_staff)
    SQL> /
    
         TOTAL   Q1_STAFF  Q13_STAFF
    ---------- ---------- ----------
             1          2
             1          5
             1
             3                                 5
    when i insert one more record, it becomes;
         TOTAL   Q1_STAFF  Q13_STAFF
    ---------- ---------- ----------
             1          2
             1          5
             2
             4                              5
    why?
    the select count(*) from css_survey returns 4 records.
  • 4. Re: with clause query with self join
    jeneesh Guru
    Currently Being Moderated
    Post complete sample data (CREATE TABLE and INSERT statements)

    And expected outoput..
  • 5. Re: with clause query with self join
    Maahjoor Newbie
    Currently Being Moderated
    thanks janeesh,

    but the office time is now over.

    i will post tomorow as you directed.

    thanks again.
  • 6. Re: with clause query with self join
    Maahjoor Newbie
    Currently Being Moderated
    okay,
    below is the detail;
    create table css_survey(survey_id number primary key,
    user_id varchar2(30),user_type varchar2(10),academic_semester varchar2(30),
    q1 number,q2 number);
    insert into css_survey values(1,'et04','staff','2010',1,3);
    insert into css_survey values(2,'et03','staff','2010',1,4);
    insert into css_survey values(3,'et05','staff','2010',2,3);
    insert into css_survey values(4,'et06','staff','2010',4,4);
    insert into css_survey values(5,'et07','staff','2010',4,4);
    insert into css_survey values(6,'et08','staff','2010',5,5);
    commit;
    select count(user_id) as total_users,q1,q2
    from css_survey
    group by grouping sets(q1,q2);
    
    TOTAL_USERS         Q1         Q2
    ----------- ---------- ----------
              2                  1
              1                  2
              2                  4
              1                  5
              3                                  4
              1                                  5
              2                                  3
    
    7 rows selected.
    and ofcourse this is my desired result.

    thank you so much.
  • 7. Re: with clause query with self join
    sb92075 Guru
    Currently Being Moderated
    CREATE TABLE css_survey 
      ( 
         survey_id         NUMBER PRIMARY KEY, 
         user_id           VARCHAR2(30), 
         user_type         VARCHAR2(10), 
         academic_semester VARCHAR2(30), 
         q1                NUMBER, 
         q2                NUMBER 
      ); 

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points