sql - TSQL- How to count number of instances -

-

i'm using sql server 2014 , need know set of students have action code used once, , students have action code used more once.

enter image description here

i'm trying see if student has more 1 of 5 action codes.
e.g. 

select studentid  #t  actioncode in (51201,51206,51207,51208,51209)

how code please?

thank help.

select    studentid,    actioncode,    actioncodecount = count(*)    dbo.studentactioncodes    actioncode in (51201, 51206, 51207, 51208, 51209) group    studentid,    actioncode order    studentid desc,    actioncode ;

see working in live demo @ sqlfiddle

given set of values (including unwanted actioncodes):

studentid   actioncode ----------- ----------- 987654      51201 987654      51206 987654      51207 987654      51208 987654      51209 987654      51210 987653      51201 987653      51208 987653      51208 987653      51211 987652      51201 987652      51206 987652      51206 987652      51207 987652      51208 987652      51209 987652      51212 987652      51213 987651      51201 987651      51206 987651      51209 987651      51209 987651      51214 987651      51215 987650      51201 987650      51201 987650      51201 987650      51201 987650      51208 987650      51208 987650      51216

here results:

studentid   actioncode  actioncodecount ----------- ----------- --------------- 987654      51201       1 987654      51206       1 987654      51207       1 987654      51208       1 987654      51209       1 987653      51201       1 987653      51208       2 987652      51201       1 987652      51206       2 987652      51207       1 987652      51208       1 987652      51209       1 987651      51201       1 987651      51206       1 987651      51209       2 987650      51201       4 987650      51208       2

you might enjoy query:

select    *    dbo.studentactioncodes s    pivot (       count(s.actioncode) s.actioncode in ([51201], [51206], [51207], [51208], [51209])    ) p ;

which gives result:

studentid   51201       51206       51207       51208       51209 ----------- ----------- ----------- ----------- ----------- ----------- 987650      4           0           0           2           0 987651      1           1           0           0           2 987652      1           2           1           1           1 987653      1           0           0           2           0 987654      1           1           1           1           1

Comments

Post a Comment

Popular posts from this blog

yii2 - Yii 2 Running a Cron in the basic template -

-
i have seen instructions on running cron in advanced template, cant figure out how on basic template. have following controller in basic controllers folder. <?php namespace app\controllers; use yii\console\controller; /** * cron controller */ class croncontroller extends controller { public function actionindex() { echo "cron service runnning"; } public function actionmail($to) { echo "sending mail " . $to; } } i have navigated root of application , tried these comands yii cron php yii cron im getting unknown comand "cron" i know old question failed find actual answer this. if @ code yii exec file you'll notice requires /common/config/aliases.php file console/config/main.php. can naturally change these or copy on these advanced template. hope solves problem wanting same thing.
Read more

asp.net - 'System.Web.HttpContext' does not contain a definition for 'GetOwinContext' Mystery -

-
i realize question might seem trivial some, it's these types of things find myself fighting quite bit , want make sense of despite seeming losing battle in .net (for me anyway). so, if following: using system.web; ... applicationuser user = system.web.httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); that produces error in title , red getowincontext() , error cannot resolve symbol 'getowincontext()' however, if following (remove system.web in front of httpcontext ), works expected (or @ least no errors): using system.web; ... applicationuser user = httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); however, if (same line that's working using system.web commented out): //using system.web; ... applicationuser user = httpcontext.getowincontext().getusermana...
Read more

mercurial graft feature, can it copy? -

-
there new feature in mercurial 3, called 'graft' (graft local). job of moving change-set different branch. is there way "copy" change-set branch? i interested in in moving stuff qa branch production branch, still need code reside in qa. want copy. found , have tried few things, maybe it'll become clear more tinkering , reading... does have better way of 'cherry picking' change-sets move cross branch (or trunk)??? graft copies changesets, doesn't move them. hg graft (emphasis mine): this command uses mercurial's merge logic copy individual changes other branches without merging branches in history graph. known 'backporting' or 'cherry-picking' . default, graft copy user, date, , description source changesets.
Read more