SQL summarise based on condition
Let's say I have a table called Table1 that contains three columns, VALUE, CODE and TYPE. The column VALUE contains scores ranging from 1-4 for different questions. The column CODE contains the code pertaining to the question. The column TYPE indicates whether the question is part of a paper (PAP) or online (ONL) survey.
Here's a glimpse of what the table looks like.
#Table1
VALUE CODE TYPE
----- ------ ------
2 Q2 ONL
1 Q2 PAP
3 Q2 ONL
4 Q2 ONL
1 Q2 ONL
1 Q2 ONL
1 Q2 PAP
2 Q2 ONL
4 Q2 ONL
2 Q2 ONL
3 Q1 ONL
1 Q1 ONL
4 Q1 ONL
3 Q1 ONL
and so on...
My Objective
To summarize this table using an SQL query (Oracle) such that-
1) I first count all values that are either 1's OR 2's for CODE Q2
2) And divide that number by the total number of values for Q2
3) Group results by TYPE so that I have grouped results for both ONL and PAP.
Intended Outcome
So in our case, the result of this query would be -
TYPE SCORE
PAP 0.625 (5/8)
ONL 1 (2/2)
My Attempt
As simple as this seems, I cannot seem to get the right value. I feel this requires a nested query to store the different values but I am unable to find the right example to guide me. Here's my incorrect attempt at it-
SELECT TYPE, VALUE/COUNT(VALUE) as SCORE FROM Table1
where
CODE = 'Q2' AND
VALUE in (1,2)
GROUP BY TYPE
Any help will be greatly appreciated.
sql oracle aggregate summarize
asked 2 days ago
VarunVarun
19410
add a comment |
Let's say I have a table called Table1 that contains three columns, VALUE, CODE and TYPE. The column VALUE contains scores ranging from 1-4 for different questions. The column CODE contains the code pertaining to the question. The column TYPE indicates whether the question is part of a paper (PAP) or online (ONL) survey.
Here's a glimpse of what the table looks like.
#Table1
VALUE CODE TYPE
----- ------ ------
2 Q2 ONL
1 Q2 PAP
3 Q2 ONL
4 Q2 ONL
1 Q2 ONL
1 Q2 ONL
1 Q2 PAP
2 Q2 ONL
4 Q2 ONL
2 Q2 ONL
3 Q1 ONL
1 Q1 ONL
4 Q1 ONL
3 Q1 ONL
and so on...
My Objective
To summarize this table using an SQL query (Oracle) such that-
1) I first count all values that are either 1's OR 2's for CODE Q2
2) And divide that number by the total number of values for Q2
3) Group results by TYPE so that I have grouped results for both ONL and PAP.
Intended Outcome
So in our case, the result of this query would be -
TYPE SCORE
PAP 0.625 (5/8)
ONL 1 (2/2)
My Attempt
As simple as this seems, I cannot seem to get the right value. I feel this requires a nested query to store the different values but I am unable to find the right example to guide me. Here's my incorrect attempt at it-
SELECT TYPE, VALUE/COUNT(VALUE) as SCORE FROM Table1
where
CODE = 'Q2' AND
VALUE in (1,2)
GROUP BY TYPE
Any help will be greatly appreciated.
sql oracle aggregate summarize
asked 2 days ago
VarunVarun
19410
what is the output you got?
– saravanatn
2 days ago
providing create and insert statement will help
– saravanatn
2 days ago
Are the numbers in your expected output attached to the wrong type? Shouldn't it bePAP 1,ONL 0.625?
– Boneist
2 days ago
add a comment |
Let's say I have a table called Table1 that contains three columns, VALUE, CODE and TYPE. The column VALUE contains scores ranging from 1-4 for different questions. The column CODE contains the code pertaining to the question. The column TYPE indicates whether the question is part of a paper (PAP) or online (ONL) survey.
Here's a glimpse of what the table looks like.
#Table1
VALUE CODE TYPE
----- ------ ------
2 Q2 ONL
1 Q2 PAP
3 Q2 ONL
4 Q2 ONL
1 Q2 ONL
1 Q2 ONL
1 Q2 PAP
2 Q2 ONL
4 Q2 ONL
2 Q2 ONL
3 Q1 ONL
1 Q1 ONL
4 Q1 ONL
3 Q1 ONL
and so on...
My Objective
To summarize this table using an SQL query (Oracle) such that-
1) I first count all values that are either 1's OR 2's for CODE Q2
2) And divide that number by the total number of values for Q2
3) Group results by TYPE so that I have grouped results for both ONL and PAP.
Intended Outcome
So in our case, the result of this query would be -
TYPE SCORE
PAP 0.625 (5/8)
ONL 1 (2/2)
My Attempt
As simple as this seems, I cannot seem to get the right value. I feel this requires a nested query to store the different values but I am unable to find the right example to guide me. Here's my incorrect attempt at it-
SELECT TYPE, VALUE/COUNT(VALUE) as SCORE FROM Table1
where
CODE = 'Q2' AND
VALUE in (1,2)
GROUP BY TYPE
Any help will be greatly appreciated.
sql oracle aggregate summarize
asked 2 days ago
VarunVarun
19410
Let's say I have a table called Table1 that contains three columns, VALUE, CODE and TYPE. The column VALUE contains scores ranging from 1-4 for different questions. The column CODE contains the code pertaining to the question. The column TYPE indicates whether the question is part of a paper (PAP) or online (ONL) survey.
Here's a glimpse of what the table looks like.
#Table1
VALUE CODE TYPE
----- ------ ------
2 Q2 ONL
1 Q2 PAP
3 Q2 ONL
4 Q2 ONL
1 Q2 ONL
1 Q2 ONL
1 Q2 PAP
2 Q2 ONL
4 Q2 ONL
2 Q2 ONL
3 Q1 ONL
1 Q1 ONL
4 Q1 ONL
3 Q1 ONL
and so on...
My Objective
To summarize this table using an SQL query (Oracle) such that-
1) I first count all values that are either 1's OR 2's for CODE Q2
2) And divide that number by the total number of values for Q2
3) Group results by TYPE so that I have grouped results for both ONL and PAP.
Intended Outcome
So in our case, the result of this query would be -
TYPE SCORE
PAP 0.625 (5/8)
ONL 1 (2/2)
My Attempt
As simple as this seems, I cannot seem to get the right value. I feel this requires a nested query to store the different values but I am unable to find the right example to guide me. Here's my incorrect attempt at it-
SELECT TYPE, VALUE/COUNT(VALUE) as SCORE FROM Table1
where
CODE = 'Q2' AND
VALUE in (1,2)
GROUP BY TYPE
Any help will be greatly appreciated.
sql oracle aggregate summarize
sql oracle aggregate summarize
asked 2 days ago
VarunVarun
19410
asked 2 days ago
VarunVarun
19410
asked 2 days ago
VarunVarun
19410
asked 2 days ago
VarunVarun
19410
asked 2 days ago
VarunVarun
19410
19410
what is the output you got?
– saravanatn
2 days ago
providing create and insert statement will help
– saravanatn
2 days ago
Are the numbers in your expected output attached to the wrong type? Shouldn't it bePAP 1,ONL 0.625?
– Boneist
2 days ago
add a comment |
what is the output you got?
– saravanatn
2 days ago
providing create and insert statement will help
– saravanatn
2 days ago
Are the numbers in your expected output attached to the wrong type? Shouldn't it bePAP 1,ONL 0.625?
– Boneist
2 days ago
what is the output you got?
– saravanatn
2 days ago
what is the output you got?
– saravanatn
2 days ago
providing create and insert statement will help
– saravanatn
2 days ago
providing create and insert statement will help
– saravanatn
2 days ago
Are the numbers in your expected output attached to the wrong type? Shouldn't it be
PAP 1, ONL 0.625?– Boneist
2 days ago
Are the numbers in your expected output attached to the wrong type? Shouldn't it be
PAP 1, ONL 0.625?– Boneist
2 days ago
add a comment |
3 Answers
3
active
oldest
votes
Several ways to do this.
The problem with your query is that you didn't execute step two - getting the TOTAL number of responses for Q2.
Here's my SQLFiddle: http://www.sqlfiddle.com/#!9/898b3f/7
SELECT TYPE,
COUNT(VALUE),
(select count(*) from table1 where code = 'Q2') ,
COUNT(VALUE) / (select count(*) from table1 where code = 'Q2') as score
FROM Table1
where
CODE = 'Q2' AND
VALUE in (1,2)
GROUP BY TYPE
answered 2 days ago
Neville KuytNeville Kuyt
22.4k2538
add a comment |
You can simply do:
select type, avg(case when value in (1, 2) then 1.0 else 0 end)
from table1
where code = 'Q2'
group by type;
answered 2 days ago
Gordon LinoffGordon Linoff
765k35296400
add a comment |
Here it is:
select type, cnt_value/sum_value as score
FROM (
SELECT type, count (value) as cnt_value , sum (value) as sum_value
FROM Table1
WHERE
CODE = 'Q2' AND
VALUE in (1,2)
GROUP by type
)
answered 2 days ago
Ted at ORCL.ProTed at ORCL.Pro
66818
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54252762%2fsql-summarise-based-on-condition%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Several ways to do this.
The problem with your query is that you didn't execute step two - getting the TOTAL number of responses for Q2.
Here's my SQLFiddle: http://www.sqlfiddle.com/#!9/898b3f/7
SELECT TYPE,
COUNT(VALUE),
(select count(*) from table1 where code = 'Q2') ,
COUNT(VALUE) / (select count(*) from table1 where code = 'Q2') as score
FROM Table1
where
CODE = 'Q2' AND
VALUE in (1,2)
GROUP BY TYPE
answered 2 days ago
Neville KuytNeville Kuyt
22.4k2538
add a comment |
Several ways to do this.
The problem with your query is that you didn't execute step two - getting the TOTAL number of responses for Q2.
Here's my SQLFiddle: http://www.sqlfiddle.com/#!9/898b3f/7
SELECT TYPE,
COUNT(VALUE),
(select count(*) from table1 where code = 'Q2') ,
COUNT(VALUE) / (select count(*) from table1 where code = 'Q2') as score
FROM Table1
where
CODE = 'Q2' AND
VALUE in (1,2)
GROUP BY TYPE
answered 2 days ago
Neville KuytNeville Kuyt
22.4k2538
add a comment |
Several ways to do this.
The problem with your query is that you didn't execute step two - getting the TOTAL number of responses for Q2.
Here's my SQLFiddle: http://www.sqlfiddle.com/#!9/898b3f/7
SELECT TYPE,
COUNT(VALUE),
(select count(*) from table1 where code = 'Q2') ,
COUNT(VALUE) / (select count(*) from table1 where code = 'Q2') as score
FROM Table1
where
CODE = 'Q2' AND
VALUE in (1,2)
GROUP BY TYPE
answered 2 days ago
Neville KuytNeville Kuyt
22.4k2538
Several ways to do this.
The problem with your query is that you didn't execute step two - getting the TOTAL number of responses for Q2.
Here's my SQLFiddle: http://www.sqlfiddle.com/#!9/898b3f/7
SELECT TYPE,
COUNT(VALUE),
(select count(*) from table1 where code = 'Q2') ,
COUNT(VALUE) / (select count(*) from table1 where code = 'Q2') as score
FROM Table1
where
CODE = 'Q2' AND
VALUE in (1,2)
GROUP BY TYPE
answered 2 days ago
Neville KuytNeville Kuyt
22.4k2538
answered 2 days ago
Neville KuytNeville Kuyt
22.4k2538
answered 2 days ago
Neville KuytNeville Kuyt
22.4k2538
answered 2 days ago
Neville KuytNeville Kuyt
22.4k2538
22.4k2538
add a comment |
add a comment |
You can simply do:
select type, avg(case when value in (1, 2) then 1.0 else 0 end)
from table1
where code = 'Q2'
group by type;
answered 2 days ago
Gordon LinoffGordon Linoff
765k35296400
add a comment |
You can simply do:
select type, avg(case when value in (1, 2) then 1.0 else 0 end)
from table1
where code = 'Q2'
group by type;
answered 2 days ago
Gordon LinoffGordon Linoff
765k35296400
add a comment |
You can simply do:
select type, avg(case when value in (1, 2) then 1.0 else 0 end)
from table1
where code = 'Q2'
group by type;
answered 2 days ago
Gordon LinoffGordon Linoff
765k35296400
You can simply do:
select type, avg(case when value in (1, 2) then 1.0 else 0 end)
from table1
where code = 'Q2'
group by type;
answered 2 days ago
Gordon LinoffGordon Linoff
765k35296400
answered 2 days ago
Gordon LinoffGordon Linoff
765k35296400
answered 2 days ago
Gordon LinoffGordon Linoff
765k35296400
answered 2 days ago
Gordon LinoffGordon Linoff
765k35296400
765k35296400
add a comment |
add a comment |
Here it is:
select type, cnt_value/sum_value as score
FROM (
SELECT type, count (value) as cnt_value , sum (value) as sum_value
FROM Table1
WHERE
CODE = 'Q2' AND
VALUE in (1,2)
GROUP by type
)
answered 2 days ago
Ted at ORCL.ProTed at ORCL.Pro
66818
add a comment |
Here it is:
select type, cnt_value/sum_value as score
FROM (
SELECT type, count (value) as cnt_value , sum (value) as sum_value
FROM Table1
WHERE
CODE = 'Q2' AND
VALUE in (1,2)
GROUP by type
)
answered 2 days ago
Ted at ORCL.ProTed at ORCL.Pro
66818
add a comment |
Here it is:
select type, cnt_value/sum_value as score
FROM (
SELECT type, count (value) as cnt_value , sum (value) as sum_value
FROM Table1
WHERE
CODE = 'Q2' AND
VALUE in (1,2)
GROUP by type
)
answered 2 days ago
Ted at ORCL.ProTed at ORCL.Pro
66818
Here it is:
select type, cnt_value/sum_value as score
FROM (
SELECT type, count (value) as cnt_value , sum (value) as sum_value
FROM Table1
WHERE
CODE = 'Q2' AND
VALUE in (1,2)
GROUP by type
)
answered 2 days ago
Ted at ORCL.ProTed at ORCL.Pro
66818
answered 2 days ago
Ted at ORCL.ProTed at ORCL.Pro
66818
answered 2 days ago
Ted at ORCL.ProTed at ORCL.Pro
66818
answered 2 days ago
Ted at ORCL.ProTed at ORCL.Pro
66818
66818
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54252762%2fsql-summarise-based-on-condition%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
what is the output you got?
– saravanatn
2 days ago
providing create and insert statement will help
– saravanatn
2 days ago
Are the numbers in your expected output attached to the wrong type? Shouldn't it be
PAP 1,ONL 0.625?– Boneist
2 days ago