SQL Divide number of ID's from 2 different tables?
0
I have the following problem, I have 2 tables:
Surveys (surveyID, topic, text)
Response (responseID, response_text, response_value)
My problem is the following: how do I get the average number of responses per survey?
Assuming I have 2 SurveyID and 8 AnswerID, how can I divide the 8 responseID by the 2 surveyID?
My thought was but this does not work out:
SELECT (COUNT(surveyID) / COUNT(responseID))
FROM Surveys, Response;
sql
edited Nov 24 '18 at 16:03
Littlefoot
21k71433
asked Nov 24 '18 at 15:55
maxi2227maxi2227
1
add a comment |
0
I have the following problem, I have 2 tables:
Surveys (surveyID, topic, text)
Response (responseID, response_text, response_value)
My problem is the following: how do I get the average number of responses per survey?
Assuming I have 2 SurveyID and 8 AnswerID, how can I divide the 8 responseID by the 2 surveyID?
My thought was but this does not work out:
SELECT (COUNT(surveyID) / COUNT(responseID))
FROM Surveys, Response;
sql
edited Nov 24 '18 at 16:03
Littlefoot
21k71433
asked Nov 24 '18 at 15:55
maxi2227maxi2227
1
1
I think your database design is flawed, and there should be some relationship between the survey and response tables. Also, tag your question with the actual database you are using (e.g. MySQL, Oracle, SQL Server). SQL is just a language, not a product.
– Tim Biegeleisen
Nov 24 '18 at 16:01
What is the error? or the output you got is unexpected
– Himanshu Ahuja
Nov 24 '18 at 16:33
Okay thankt you, I'll be more accurate the next time :-) @TimBiegeleisen
– maxi2227
Nov 24 '18 at 16:42
@HimanshuAhuja the output is unexpected, the result I get is '1'.
– maxi2227
Nov 24 '18 at 16:43
SELECT (COUNT(distinct surveyID) / COUNT(distinct responseID)) FROM Surveys, Response. The only reason being for unexpected output is the duplicates seperately in your tables use this youll get the desired.
– Himanshu Ahuja
Nov 24 '18 at 16:46
add a comment |
0
0
0
I have the following problem, I have 2 tables:
Surveys (surveyID, topic, text)
Response (responseID, response_text, response_value)
My problem is the following: how do I get the average number of responses per survey?
Assuming I have 2 SurveyID and 8 AnswerID, how can I divide the 8 responseID by the 2 surveyID?
My thought was but this does not work out:
SELECT (COUNT(surveyID) / COUNT(responseID))
FROM Surveys, Response;
sql
edited Nov 24 '18 at 16:03
Littlefoot
21k71433
asked Nov 24 '18 at 15:55
maxi2227maxi2227
1
I have the following problem, I have 2 tables:
Surveys (surveyID, topic, text)
Response (responseID, response_text, response_value)
My problem is the following: how do I get the average number of responses per survey?
Assuming I have 2 SurveyID and 8 AnswerID, how can I divide the 8 responseID by the 2 surveyID?
My thought was but this does not work out:
SELECT (COUNT(surveyID) / COUNT(responseID))
FROM Surveys, Response;
sql
sql
edited Nov 24 '18 at 16:03
Littlefoot
21k71433
asked Nov 24 '18 at 15:55
maxi2227maxi2227
1
edited Nov 24 '18 at 16:03
Littlefoot
21k71433
asked Nov 24 '18 at 15:55
maxi2227maxi2227
1
edited Nov 24 '18 at 16:03
Littlefoot
21k71433
edited Nov 24 '18 at 16:03
Littlefoot
21k71433
edited Nov 24 '18 at 16:03
Littlefoot
21k71433
21k71433
asked Nov 24 '18 at 15:55
maxi2227maxi2227
1
asked Nov 24 '18 at 15:55
maxi2227maxi2227
1
asked Nov 24 '18 at 15:55
maxi2227maxi2227
1
1
1
I think your database design is flawed, and there should be some relationship between the survey and response tables. Also, tag your question with the actual database you are using (e.g. MySQL, Oracle, SQL Server). SQL is just a language, not a product.
– Tim Biegeleisen
Nov 24 '18 at 16:01
What is the error? or the output you got is unexpected
– Himanshu Ahuja
Nov 24 '18 at 16:33
Okay thankt you, I'll be more accurate the next time :-) @TimBiegeleisen
– maxi2227
Nov 24 '18 at 16:42
@HimanshuAhuja the output is unexpected, the result I get is '1'.
– maxi2227
Nov 24 '18 at 16:43
SELECT (COUNT(distinct surveyID) / COUNT(distinct responseID)) FROM Surveys, Response. The only reason being for unexpected output is the duplicates seperately in your tables use this youll get the desired.
– Himanshu Ahuja
Nov 24 '18 at 16:46
add a comment |
1
I think your database design is flawed, and there should be some relationship between the survey and response tables. Also, tag your question with the actual database you are using (e.g. MySQL, Oracle, SQL Server). SQL is just a language, not a product.
– Tim Biegeleisen
Nov 24 '18 at 16:01
What is the error? or the output you got is unexpected
– Himanshu Ahuja
Nov 24 '18 at 16:33
Okay thankt you, I'll be more accurate the next time :-) @TimBiegeleisen
– maxi2227
Nov 24 '18 at 16:42
@HimanshuAhuja the output is unexpected, the result I get is '1'.
– maxi2227
Nov 24 '18 at 16:43
SELECT (COUNT(distinct surveyID) / COUNT(distinct responseID)) FROM Surveys, Response. The only reason being for unexpected output is the duplicates seperately in your tables use this youll get the desired.
– Himanshu Ahuja
Nov 24 '18 at 16:46
1
1
I think your database design is flawed, and there should be some relationship between the survey and response tables. Also, tag your question with the actual database you are using (e.g. MySQL, Oracle, SQL Server). SQL is just a language, not a product.
– Tim Biegeleisen
Nov 24 '18 at 16:01
I think your database design is flawed, and there should be some relationship between the survey and response tables. Also, tag your question with the actual database you are using (e.g. MySQL, Oracle, SQL Server). SQL is just a language, not a product.
– Tim Biegeleisen
Nov 24 '18 at 16:01
What is the error? or the output you got is unexpected
– Himanshu Ahuja
Nov 24 '18 at 16:33
What is the error? or the output you got is unexpected
– Himanshu Ahuja
Nov 24 '18 at 16:33
Okay thankt you, I'll be more accurate the next time :-) @TimBiegeleisen
– maxi2227
Nov 24 '18 at 16:42
Okay thankt you, I'll be more accurate the next time :-) @TimBiegeleisen
– maxi2227
Nov 24 '18 at 16:42
@HimanshuAhuja the output is unexpected, the result I get is '1'.
– maxi2227
Nov 24 '18 at 16:43
@HimanshuAhuja the output is unexpected, the result I get is '1'.
– maxi2227
Nov 24 '18 at 16:43
SELECT (COUNT(distinct surveyID) / COUNT(distinct responseID)) FROM Surveys, Response. The only reason being for unexpected output is the duplicates seperately in your tables use this youll get the desired.– Himanshu Ahuja
Nov 24 '18 at 16:46
SELECT (COUNT(distinct surveyID) / COUNT(distinct responseID)) FROM Surveys, Response. The only reason being for unexpected output is the duplicates seperately in your tables use this youll get the desired.– Himanshu Ahuja
Nov 24 '18 at 16:46
add a comment |
3 Answers
3
active
oldest
votes
0
you can try this with sql-serevr:
with x as(
Select count(*) as totSurveys, 0 as TotResponse
from Surveys
union
Select 0 as totSurveys, count(*) as TotResponse
from Response
) select TotResponse/totSurveysas Result
from x
or you can :
select (Select count(*) from Surveys)/(Select count(*) from Response)
answered Nov 24 '18 at 15:59
picklerickpicklerick
32918
@TimBiegeleisen mhe, thought i was on sql-server tag, edited solution for sql-server
– picklerick
Nov 24 '18 at 16:03
add a comment |
0
To be very specific on your question:
SELECT (COUNT(distinct surveyID) / COUNT(distinct responseID))
FROM Surveys, Response;
(the distinct intructs the DBMS to count only unique values from the result)
answered Nov 24 '18 at 16:10
Marcelo MyaraMarcelo Myara
1,3931829
add a comment |
0
SELECT (COUNT(distinct surveyID) / COUNT(distinct
responseID))
FROM Surveys, Response
The only reason being for
unexpected output is the duplicates seperately in
your
tables use this youll get the desired
answered Nov 24 '18 at 16:50
Himanshu AhujaHimanshu Ahuja
6561216
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
});
}
});
draft saved
draft discarded
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%2f53459868%2fsql-divide-number-of-ids-from-2-different-tables%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
0
you can try this with sql-serevr:
with x as(
Select count(*) as totSurveys, 0 as TotResponse
from Surveys
union
Select 0 as totSurveys, count(*) as TotResponse
from Response
) select TotResponse/totSurveysas Result
from x
or you can :
select (Select count(*) from Surveys)/(Select count(*) from Response)
answered Nov 24 '18 at 15:59
picklerickpicklerick
32918
@TimBiegeleisen mhe, thought i was on sql-server tag, edited solution for sql-server
– picklerick
Nov 24 '18 at 16:03
add a comment |
0
you can try this with sql-serevr:
with x as(
Select count(*) as totSurveys, 0 as TotResponse
from Surveys
union
Select 0 as totSurveys, count(*) as TotResponse
from Response
) select TotResponse/totSurveysas Result
from x
or you can :
select (Select count(*) from Surveys)/(Select count(*) from Response)
answered Nov 24 '18 at 15:59
picklerickpicklerick
32918
@TimBiegeleisen mhe, thought i was on sql-server tag, edited solution for sql-server
– picklerick
Nov 24 '18 at 16:03
add a comment |
0
0
0
you can try this with sql-serevr:
with x as(
Select count(*) as totSurveys, 0 as TotResponse
from Surveys
union
Select 0 as totSurveys, count(*) as TotResponse
from Response
) select TotResponse/totSurveysas Result
from x
or you can :
select (Select count(*) from Surveys)/(Select count(*) from Response)
answered Nov 24 '18 at 15:59
picklerickpicklerick
32918
you can try this with sql-serevr:
with x as(
Select count(*) as totSurveys, 0 as TotResponse
from Surveys
union
Select 0 as totSurveys, count(*) as TotResponse
from Response
) select TotResponse/totSurveysas Result
from x
or you can :
select (Select count(*) from Surveys)/(Select count(*) from Response)
answered Nov 24 '18 at 15:59
picklerickpicklerick
32918
edited Nov 24 '18 at 16:02
answered Nov 24 '18 at 15:59
picklerickpicklerick
32918
answered Nov 24 '18 at 15:59
picklerickpicklerick
32918
answered Nov 24 '18 at 15:59
picklerickpicklerick
32918
32918
@TimBiegeleisen mhe, thought i was on sql-server tag, edited solution for sql-server
– picklerick
Nov 24 '18 at 16:03
add a comment |
@TimBiegeleisen mhe, thought i was on sql-server tag, edited solution for sql-server
– picklerick
Nov 24 '18 at 16:03
@TimBiegeleisen mhe, thought i was on sql-server tag, edited solution for sql-server
– picklerick
Nov 24 '18 at 16:03
@TimBiegeleisen mhe, thought i was on sql-server tag, edited solution for sql-server
– picklerick
Nov 24 '18 at 16:03
add a comment |
0
To be very specific on your question:
SELECT (COUNT(distinct surveyID) / COUNT(distinct responseID))
FROM Surveys, Response;
(the distinct intructs the DBMS to count only unique values from the result)
answered Nov 24 '18 at 16:10
Marcelo MyaraMarcelo Myara
1,3931829
add a comment |
0
To be very specific on your question:
SELECT (COUNT(distinct surveyID) / COUNT(distinct responseID))
FROM Surveys, Response;
(the distinct intructs the DBMS to count only unique values from the result)
answered Nov 24 '18 at 16:10
Marcelo MyaraMarcelo Myara
1,3931829
add a comment |
0
0
0
To be very specific on your question:
SELECT (COUNT(distinct surveyID) / COUNT(distinct responseID))
FROM Surveys, Response;
(the distinct intructs the DBMS to count only unique values from the result)
answered Nov 24 '18 at 16:10
Marcelo MyaraMarcelo Myara
1,3931829
To be very specific on your question:
SELECT (COUNT(distinct surveyID) / COUNT(distinct responseID))
FROM Surveys, Response;
(the distinct intructs the DBMS to count only unique values from the result)
answered Nov 24 '18 at 16:10
Marcelo MyaraMarcelo Myara
1,3931829
answered Nov 24 '18 at 16:10
Marcelo MyaraMarcelo Myara
1,3931829
answered Nov 24 '18 at 16:10
Marcelo MyaraMarcelo Myara
1,3931829
answered Nov 24 '18 at 16:10
Marcelo MyaraMarcelo Myara
1,3931829
1,3931829
add a comment |
add a comment |
0
SELECT (COUNT(distinct surveyID) / COUNT(distinct
responseID))
FROM Surveys, Response
The only reason being for
unexpected output is the duplicates seperately in
your
tables use this youll get the desired
answered Nov 24 '18 at 16:50
Himanshu AhujaHimanshu Ahuja
6561216
add a comment |
0
SELECT (COUNT(distinct surveyID) / COUNT(distinct
responseID))
FROM Surveys, Response
The only reason being for
unexpected output is the duplicates seperately in
your
tables use this youll get the desired
answered Nov 24 '18 at 16:50
Himanshu AhujaHimanshu Ahuja
6561216
add a comment |
0
0
0
SELECT (COUNT(distinct surveyID) / COUNT(distinct
responseID))
FROM Surveys, Response
The only reason being for
unexpected output is the duplicates seperately in
your
tables use this youll get the desired
answered Nov 24 '18 at 16:50
Himanshu AhujaHimanshu Ahuja
6561216
SELECT (COUNT(distinct surveyID) / COUNT(distinct
responseID))
FROM Surveys, Response
The only reason being for
unexpected output is the duplicates seperately in
your
tables use this youll get the desired
answered Nov 24 '18 at 16:50
Himanshu AhujaHimanshu Ahuja
6561216
answered Nov 24 '18 at 16:50
Himanshu AhujaHimanshu Ahuja
6561216
answered Nov 24 '18 at 16:50
Himanshu AhujaHimanshu Ahuja
6561216
answered Nov 24 '18 at 16:50
Himanshu AhujaHimanshu Ahuja
6561216
6561216
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53459868%2fsql-divide-number-of-ids-from-2-different-tables%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
1
I think your database design is flawed, and there should be some relationship between the survey and response tables. Also, tag your question with the actual database you are using (e.g. MySQL, Oracle, SQL Server). SQL is just a language, not a product.
– Tim Biegeleisen
Nov 24 '18 at 16:01
What is the error? or the output you got is unexpected
– Himanshu Ahuja
Nov 24 '18 at 16:33
Okay thankt you, I'll be more accurate the next time :-) @TimBiegeleisen
– maxi2227
Nov 24 '18 at 16:42
@HimanshuAhuja the output is unexpected, the result I get is '1'.
– maxi2227
Nov 24 '18 at 16:43
SELECT (COUNT(distinct surveyID) / COUNT(distinct responseID)) FROM Surveys, Response. The only reason being for unexpected output is the duplicates seperately in your tables use this youll get the desired.– Himanshu Ahuja
Nov 24 '18 at 16:46