7 Replies Latest reply: Dec 9, 2012 11:13 PM by sb92075 RSS

    with clause query with self join

    Maahjoor
      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
          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
            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
              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
                Post complete sample data (CREATE TABLE and INSERT statements)

                And expected outoput..
                • 5. Re: with clause query with self join
                  Maahjoor
                  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
                    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
                      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 
                        );