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 |
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 #650019 is a reply to message #650018] |
Tue, 12 April 2016 15:28 |
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 |
|
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 |
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 #650054 is a reply to message #650052] |
Wed, 13 April 2016 10:08 |
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
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Mar 29 06:54:29 CDT 2024
|