mysql - Failure To Execute SQL Subquery With Join -

-

please can tell me i'm not doing right. here query want execute, nothing happens when run command. i'm new sql pardon mistake, if any. 

 select  t.*,  count(distinct t.subjects) subjectenrollment,  u.urefnumber,  u.uresidence    (   select r.*,   @currank := if( @prevrank = finalscore, @currank, @incrank ) position,   @incrank := @incrank + 1,   @prevrank = finalscore   studentsreports r,    (    select @currank := 0,     @prevrank = null,     @incrank := 1   ) c   order finalscore asc  ) t left join studentstbl u on t.studref = u.urefnumber  t.author = :staff  , t.studentname = :student  , t.academicyr = :year  , t.academicterm = :term  , t.program = :program  , t.classes = :level  , t.subjects = :subject;

as can seen code, i'm trying fetch students records, , include column position in each subject, number of students offering each subject. more over, want include each student's residential status, in different table.

at point, want add accumulated raw scores, is, summation of marks obtained in each subject, don't know how make possible. friend has suggestted acheiving in separate queries, unfortunately, well. please, grateful help. in advance!

consider adjustment using derived tables , correlated subqueries without need of @variables. sql statement below handles following needs employed stategy listed alongside:

  1. column position/rank in each subject - aggregate correlated count subquery @ top level
  2. number of students offering each subject - aggregate count derived table (inner join clause)
  3. each student's residential status - group field (from clause)
  4. summation of marks in each subject - aggregate sum derived table (from clause)

sql (with binded params)

select main.student_number, main.subjects, main.student_residence,        main.accum_raw_scores,         (select count(*)          (select s.studref, s.subjects, sum(s.finalscore) total_score               studentsreports s               group s.studref, s.subjects) sub         sub.subjects = main.subjects         , sub.total_score >= main.accum_raw_scores) subject_rank,        cnt.subject_student_count   (select r.studref student_number, r.subjects, u.uresidence student_residence,            sum(r.finalscore) accum_raw_scores           studentreports r    left join studentstbl u on r.studref = u.urefnumber    r.author = :staff    , r.studentname = :student    , r.academicyr = :year    , r.academicterm = :term    , r.program = :program    , r.classes = :level    , r.subjects = :subject    group r.studref, r.subjects, u.uresidence) main inner join     (select sub.subjects, count(*) subject_student_count      studentreports sub      group sub.subjects) cnt on cnt.subjects = main.subjects

Comments

Post a Comment

Popular posts from this blog

php - How to display all orders for a single product showing the most recent first? Woocommerce -

-
i have password protected page on frontend. on page, want display full list of purchases associated single woocommerce product id. option 1: i've looked through woocommerce docs , , found: wc_cli_order , list_( $args, $assoc_args ) function lists orders. presumably, orders can filtered product id [--=] filter orders based on order property. line item fields (numeric array, started index zero): line_items.0.product_id option 2: i found article , based on customer id. i've modified code based on guesses meta_key , meta_value. $customer_orders = get_posts( array( 'numberposts' => -1, 'meta_key' => '_product', 'meta_value' => get_product_id(), 'post_type' => wc_get_order_types(), 'post_status' => array_keys( wc_get_order_statuses() ), ) ); how display orders single product showing recent order first? here solution put based on these articles: http://www.w...
Read more

asp.net - How to correctly use QUERY_STRING in ISAPI rewrite? -

-
i'm having difficult time , hope guys can help. using helicon isapi rewrite version 3.1.0.104 on our iis server. edit http.conf file , have tried , still fail. this trying do: redirect url: https://www.domain.com/switch-by-version?version=2.8.5.2594 to: https://test.domain.com/load/load.aspx?tver=2.8.5.2594 the version number @ end source url change, , need target url have same version number @ end of url example above shows. i tried following not work: rewritecond %{query_string} ^version=(\d\d?)\.(\d\d?)\.(\d\d?)\.(.*)$ [nc] rewriterule ^/switch-by-version(.*)$ https://test.domain.com/load/load.aspx?tver=%1 [r=307,nc,l] any appreciated! thanks. if it's running in equivalent context of .htaccess , rule shouldn't start / . here's simpler version: rewritecond %{query_string} ^version=(\d+\.\d+\.\d+(?:\..+)?)$ [nc] rewriterule ^switch-by-version/?$ https://test.domain.com/load/load.aspx?tver=%1 [r=307,nc,l]
Read more

angularjs - How restrict admin panel using in backend laravel and admin panel on angular? -

-
https://scotch.io/tutorials/token-based-authentication-for-angularjs-and-laravel-apps following tutorials, try write authentication restrict admin panel, didn't understood how can that. this admin panel, admin panel don't want show , request database unauthenticated users. my laravel-routes route::get('/admin',function(){ return view('index'); }); route::get('/api/v1/employees/{id?}', 'employees@index'); route::get('/api/v1/users/{id?}', 'employees@users'); route::post('/api/v1/employees', 'employees@store'); route::post('/api/v1/employees/{id}', 'employees@update'); route::delete('/api/v1/employees/{id}', 'employees@destroy'); route::group(['prefix' => 'api'], function() { route::resource('authenticate', 'authenticatecontroller', ['only' => ['index']]); route::post('authenticate', 'authentic...
Read more