Home » SQL & PL/SQL » SQL & PL/SQL » Data count for Likert Scale (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0)
Data count for Likert Scale [message #650017] Tue, 12 April 2016 14:15 Go to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
I'm trying to come up with a query that will produce results in a Likert Scale.
(i.e. 1-Strongly disagree 2-Disagree 3-Neither agree nor disagree 4-Agree 5-Strongly agree)

Just seeing if I'm on the right path or not.

CREATE TABLE RESPONSES_TEST
(
  COMPLETED_EVALUATION_ID  NUMBER               NOT NULL,
  QUESTION_ID              NUMBER               NOT NULL,
  SCORE                    NUMBER
)


Insert into RESPONSES_TEST
   (COMPLETED_EVALUATION_ID, QUESTION_ID, SCORE)
 Values
   (18617, 1, 1);
Insert into RESPONSES_TEST
   (COMPLETED_EVALUATION_ID, QUESTION_ID, SCORE)
 Values
   (18617, 1, 1);
Insert into RESPONSES_TEST
   (COMPLETED_EVALUATION_ID, QUESTION_ID, SCORE)
 Values
   (18617, 1, 1);
Insert into RESPONSES_TEST
   (COMPLETED_EVALUATION_ID, QUESTION_ID, SCORE)
 Values
   (18617, 1, 3);
Insert into RESPONSES_TEST
   (COMPLETED_EVALUATION_ID, QUESTION_ID, SCORE)
 Values
   (18617, 1, 3);
Insert into RESPONSES_TEST
   (COMPLETED_EVALUATION_ID, QUESTION_ID, SCORE)
 Values
   (18617, 1, 5);
Insert into RESPONSES_TEST
   (COMPLETED_EVALUATION_ID, QUESTION_ID, SCORE)
 Values
   (18617, 2, 5);
Insert into RESPONSES_TEST
   (COMPLETED_EVALUATION_ID, QUESTION_ID, SCORE)
 Values
   (18617, 2, 4);
Insert into RESPONSES_TEST
   (COMPLETED_EVALUATION_ID, QUESTION_ID, SCORE)
 Values
   (18617, 2, 4);
COMMIT;


select question_id,
       decode(score, 1, count(score) over (partition by question_id, score)) score1,
       decode(score, 2, count(score) over (partition by question_id, score)) score2,
       decode(score, 3, count(score) over (partition by question_id, score)) score3,
       decode(score, 4, count(score) over (partition by question_id, score)) score4,
       decode(score, 5, count(score) over (partition by question_id, score)) score5
  from responses_test


My desired output would be:

question_id, score1, score2, score3, score4, score5

1 3 0 2 0 1

2 0 0 0 2 1

It would also be nice to have:

question_id, score1, score2, score3, score4, score5, average, std deviation

1 3 0 2 0 1 3 3

2 0 0 0 2 1 2 1

Could someone point me in the right direction?

[Updated on: Tue, 12 April 2016 14:18]

Report message to a moderator

Re: Data count for Likert Scale [message #650018 is a reply to message #650017] Tue, 12 April 2016 15:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Could someone point me in the right direction?


This is a standard pivot query.
Re: Data count for Likert Scale [message #650019 is a reply to message #650018] Tue, 12 April 2016 15:28 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
Here's what I've come up with so far. I'm using a production table in this example.

I'm not sure where to put the STDDEV statement since it's using score1, score2, score3, score4 and score5. Not really sure where it belongs to get that number I need.


select id,
       section,
       text,
       score1,
       score2,
       score3,
       score4,
       score5,
       average,
       stddev(score1) over (partition by id order by score1, score2, score3, score4, score5) std
 from (select a.id,
       a.section,
       a.text,
       b.score1,
       b.score2,
       b.score3,
       b.score4,
       b.score5,
       ((b.score1 * 1) + (b.score2 * 2) + (b.score3 * 3) + (b.score4 * 4) + (b.score5 * 5)) / (b.score1 + b.score2 + b.score3 + b.score4 + b.score5) average
  from question                   a,
       (select question_id,
               max(decode(score1, null, 0, score1)) score1,
               max(decode(score2, null, 0, score2)) score2,
               max(decode(score3, null, 0, score3)) score3,
               max(decode(score4, null, 0, score4)) score4,
               max(decode(score5, null, 0, score5)) score5
          from (select question_id,
                       decode(score, 1, count(score) over (partition by question_id, score)) score1,
                       decode(score, 2, count(score) over (partition by question_id, score)) score2,
                       decode(score, 3, count(score) over (partition by question_id, score)) score3,
                       decode(score, 4, count(score) over (partition by question_id, score)) score4,
                       decode(score, 5, count(score) over (partition by question_id, score)) score5
                  from responses
                    where completed_evaluation_id = 62)
            group by question_id) b
    where a.id      = b.question_id and
          a.section = 1)

Re: Data count for Likert Scale [message #650022 is a reply to message #650017] Tue, 12 April 2016 17:07 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Since we don't have all the tables, columns, and data for your production, this is based on your original problem. The following is a simple modification of your original code. Your desired results for average and standard deviation do not appear to match your data, so those are different.

SCOTT@orcl> select * from responses_test
  2  /

COMPLETED_EVALUATION_ID QUESTION_ID      SCORE
----------------------- ----------- ----------
                  18617           1          1
                  18617           1          1
                  18617           1          1
                  18617           1          3
                  18617           1          3
                  18617           1          5
                  18617           2          5
                  18617           2          4
                  18617           2          4

9 rows selected.

SCOTT@orcl> select question_id,
  2  	    sum(decode(score, 1, 1, 0)) score1,
  3  	    sum(decode(score, 2, 1, 0)) score2,
  4  	    sum(decode(score, 3, 1, 0)) score3,
  5  	    sum(decode(score, 4, 1, 0)) score4,
  6  	    sum(decode(score, 5, 1, 0)) score5,
  7  	    round(avg(score))		average,
  8  	    round(stddev(score))	std_deviation
  9    from responses_test
 10   group by question_id
 11  /

QUESTION_ID     SCORE1     SCORE2     SCORE3     SCORE4     SCORE5    AVERAGE STD_DEVIATION
----------- ---------- ---------- ---------- ---------- ---------- ---------- -------------
          1          3          0          2          0          1          2             2
          2          0          0          0          2          1          4             1

2 rows selected.


Re: Data count for Likert Scale [message #650047 is a reply to message #650022] Wed, 13 April 2016 08:59 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
Thanks Barbara. I was close but also quite far away based on your code. At least I tried to come up with something that sort of worked. That's why I was asking if I was on the right track.
Re: Data count for Likert Scale [message #650052 is a reply to message #650047] Wed, 13 April 2016 10:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And you were not, no analytic functions, just basic standard pivot as I said.

Re: Data count for Likert Scale [message #650054 is a reply to message #650052] Wed, 13 April 2016 10:08 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
I don't believe I understand what a standard pivot is. I'm asking for clarification here.

This is a standard pivot:


select question_id,
    	    sum(decode(score, 1, 1, 0)) score1,
    	    sum(decode(score, 2, 1, 0)) score2,
    	    sum(decode(score, 3, 1, 0)) score3,
    	    sum(decode(score, 4, 1, 0)) score4,
    	    sum(decode(score, 5, 1, 0)) score5,
    	    round(avg(score))		average,
    	    round(stddev(score))	std_deviation
      from responses_test
        group by question_id



This is not a standard pivot: (maybe it is excluding out the analytic functions)


select question_id,
               max(decode(score1, null, 0, score1)) score1,
               max(decode(score2, null, 0, score2)) score2,
               max(decode(score3, null, 0, score3)) score3,
               max(decode(score4, null, 0, score4)) score4,
               max(decode(score5, null, 0, score5)) score5
          from (select question_id,
                       decode(score, 1, count(score) over (partition by question_id, score)) score1,
                       decode(score, 2, count(score) over (partition by question_id, score)) score2,
                       decode(score, 3, count(score) over (partition by question_id, score)) score3,
                       decode(score, 4, count(score) over (partition by question_id, score)) score4,
                       decode(score, 5, count(score) over (partition by question_id, score)) score5
                  from responses
                    where completed_evaluation_id = 62)
            group by question_id



Re: Data count for Likert Scale [message #650055 is a reply to message #650054] Wed, 13 April 2016 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
This is a standard pivot


Yes.

Quote:
This is not a standard pivot


Yes, the query is plain wrong, you use analytic functions when it is not appropriate. You want to aggregate, so use aggregate functions.
Look at the output of your inner query and ask yourself "why do I generate this result set? Do I really need to have such duplicates rows?".

Re: Data count for Likert Scale [message #650057 is a reply to message #650055] Wed, 13 April 2016 10:34 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
This is good stuff. I guess in my mind I was thinking I had to use an analytic function and then flip the columns. Sounds like I was over thinking things.
Re: Data count for Likert Scale [message #650059 is a reply to message #650057] Wed, 13 April 2016 10:47 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As you are in 11g, you can just use the PIVOT clause without thinking anymore. Smile
SQL> select *
  2  from responses_test
  3  pivot (count(score)
  4         for score in (1 "SCORE1", 2 "SCORE2", 3 "SCORE3", 4 "SCORE4", 5 "SCORE5"))
  5  /
COMPLETED_EVALUATION_ID QUESTION_ID     SCORE1     SCORE2     SCORE3     SCORE4     SCORE5
----------------------- ----------- ---------- ---------- ---------- ---------- ----------
                  18617           1          3          0          2          0          1
                  18617           2          0          0          0          2          1

2 rows selected.


Previous Topic: Need help to build dynamic query
Next Topic: Create a trigger to call a procedure
Goto Forum:
  


Current Time: Fri Mar 29 06:54:29 CDT 2024