sql - How to combine different queries results into a single query result table -



sql - How to combine different queries results into a single query result table -

here thing.i trying find query can include there 3 results. i know how query 1 of them each.

questions: each survey has had @ to the lowest degree 200 members participate, provide next information: 1）survey id , survey description 2）number of members started survey 3) number of members finished.

query survey id , survey description have @ to the lowest degree 200 participations

1)

select survey_id, survey_desc, count(tbl_survey.member_id) totalnumber tbl_survey,tbl_member_participation tbl-survey.member_id = tbl_member_participation.member_id grouping survey_id, survey_desc having totalnumber >= 200

2) query number of members started not finished.

select count(survey_id) tbl_survey, survey_id exists ( select survey_id, survey_desc, count(tbl_survey.member_id) totalnumber tbl_survey,tbl_member_participation tbl-survey.member_id = tbl_member_participation.member_id grouping survey_id, survey_desc having totalnumber >= 200) , finishi_date **null** grouping survey_id

3) query number of members finished.

select count(survey_id) tbl_survey, survey_id exists ( select survey_id, survey_desc, count(tbl_survey.member_id) totalnumber tbl_survey,tbl_member_participation tbl-survey.member_id = tbl_member_participation.member_id grouping survey_id, survey_desc having totalnumber >= 200) , finishi_date ***not null*** grouping survey_id

so want way can combine these 3 results survey id, survey desc, start people, finish people. in 1 query table.

first of all, should queries right.

1) first query okay, should not utilize out-dated comma-separated bring together syntax. utilize explicit joins instead (that introduced in sql standard 20 years ago, btw).

select s.survey_id, s.survey_desc, count(*) totalnumber tbl_survey s bring together tbl_member_participation mp on s.member_id = mp.member_id grouping s.survey_id, s.survey_desc having count(*) >= 200;

2,3) sec , 3rd queries syntactically broken. kind of mixing exists , in. finishi_date fellow member of tbl_member_participation, must bring together table again. here query 2 corrected:

select s.survey_id, count(*) tbl_survey s bring together tbl_member_participation mp on s.member_id = mp.member_id survey_id in ( select s.survey_id tbl_survey s bring together tbl_member_participation mp on s.member_id = mp.member_id grouping s.survey_id having count(*) >= 200 ) , mp.finishi_date null grouping s.survey_id;

in order combine three, don't have utilize exists or in. info needed available in query 1 already. look, how modify query 1 much simpler query 2:

select s.survey_id, sum(case when mp.finishi_date null 1 else 0 end) count_unfinished tbl_survey s bring together tbl_member_participation mp on s.member_id = mp.member_id grouping s.survey_id having count(*) >= 200;

having said this, final query this:

select s.survey_id, s.survey_desc, sum(case when mp.finishi_date null 1 else 0 end) count_unfinished, sum(case when mp.finishi_date not null 1 else 0 end) count_finished, count(*) totalnumber tbl_survey s bring together tbl_member_participation mp on s.member_id = mp.member_id grouping s.survey_id, s.survey_desc having count(*) >= 200;

sql operation

-

Comments

Post a Comment

Popular posts from this blog

formatting - SAS SQL Datepart function returning odd values -

formatting - SAS SQL Datepart function returning odd values - i'm having massive problem project can't seem right. i'm trying modify info variable in datetime format. first info 30mar12:00:00:00. i've been advised utilize code below returns value 30mar60:5:30:00. want date component , have no thought how info got exported in format wasn't in microsoft access. i've tried several mods code 1 time again comes blank or sends error message. proc sql; update dataset set date = datepart(date); quit; any advice appreciated. i'm guessing variable date numeric , formatted datetime. 1 time you've carried out conversion, alter format date9. sas datetime value number of seconds since 01 jan 1960, whereas dates number of days since 01 jan 1960. both stored numbers, using right format display value key. illustration below. data _null_; a='30mar12:00:00:00'dt; b=datepart(a); c=b; format b datetime. c date9.; set b c; run; ...
Read more

c++ - Apple Mach-O Linker Error(Duplicate Symbols For Architecture armv7) -

c++ - Apple Mach-O Linker Error(Duplicate Symbols For Architecture armv7) - here error: duplicate symbol _objc_metaclass_$_bfapplinkreturntorefererview in: /users/user/documents/facebooksdk/facebooksdk.framework/facebooksdk(bfapplinkreturntorefererview.o) /users/user/desktop/project/project/project/assets/facebook/editor/ios/facebooksdk/facebooksdk.a(bfapplinkreturntorefererview.o) ld: 889 duplicate symbols architecture armv7 clang: error: linker command failed exit code 1 (use -v see invocation) i have searched find solution of error couldn't. there tons of similar error this, 1 hasn't been asked yet. problem shows when add together "-objc" "other linker flags". need add together utilize admob service. problem? how can solve this? i faced same issue while integrating facebook-ios-sdk. because using 3rd party library had bolts.framework linked against it. solution found incorporate facebook-ios-sdk source code hosted on github. ...
Read more

php - Yii 2: Unable to find a class into the extension 'yii2-admin' -

php - Yii 2: Unable to find a class into the extension 'yii2-admin' - extension's github project: https://github.com/mdmsoft/yii2-admin i'm using advanced template of yii2, i've backend , frontend , common folders advanced folder. the advanced folder on same level of vendor folder. i've others extensions , working then installed yii2-admin composer php composer.phar require mdmsoft/yii2-admin "*" installation worked well. into vendor folder i've mdmsoft folder yii2-admin subfolder it. this actual content of v endor/yiisoft/extensions.php (modified composer installation, haven't manually touched it) <?php $vendordir = dirname(__dir__); homecoming array ( 'yiisoft/yii2-jui' => array ( 'name' => 'yiisoft/yii2-jui', 'version' => '2.0.0.0', 'alias' => array ( '@yii/jui' => $vendordir . '/yiisoft/yii2-jui', ...
Read more