subquery - MySql: Calculate a value based on multiple sub-queries -

-

i have query calculates value based on several sub-queries. sub-queries based on specific timestamp. calculate value records many different timestamps. can't quite figure out how it.

the basic formula (s11s4createsessionreqrcvd - s11s4createsessionrespaccsent) * 100 / s11s4createsessionrespaccsent

select  ((((select ref_data test.sgw_s5s11 timestamp = "2013-08-21 00:00:06" , ref_type = "s11s4createsessionreqrcvd" ) - (select ref_data test.sgw_s5s11 timestamp = "2013-08-21 00:00:06" , ref_type = "s11s4createsessionrespaccsent")) * 100) / (select ref_data test.sgw_s5s11 timestamp = "2013-08-21 00:00:06" , ref_type = "s11s4createsessionreqrcvd")) mycalc

i don't know how paste in table here's sample of data in csv format

here's sample of data. (i don't know how put table in here it's in csv format)

mykey,timestamp,ref_type,ref_data 1016101,"2013-08-21 00:00:06",s5s8createsessionreqsent,128042907 1016102,"2013-08-21 00:00:06",s5s8createsessionrespaccrcvd,127088838 1016103,"2013-08-21 00:00:06",s5s8createsessionresprejrcvd,615553 1016104,"2013-08-21 00:00:06",s5s8createbearerreqrcvd,10047 1016105,"2013-08-21 00:00:06",s5s8createbearerrespaccsent,9932 1016106,"2013-08-21 00:00:06",s5s8createbearerresprejsent,103 1016107,"2013-08-21 00:00:06",s11s4createsessionreqrcvd,128255390 1016108,"2013-08-21 00:00:06",s11s4createsessionrespaccsent,127114539 1016109,"2013-08-21 00:00:06",s11s4createsessionresprejsent,713325 1016110,"2013-08-21 00:00:06",s11s4createbearerreqsent,10028 1016111,"2013-08-21 00:00:06",s11s4createbearerrespaccrcvd,9932 1016112,"2013-08-21 00:00:06",s11s4createbearerresprejrcvd,42

any appreciated!!!

this query awkward because have stored attributes on separate rows. design called entity-attribute-value, , it's bad idea relational database.

the following query might little more efficient , easier write:

select (eav.reqrcvd - eav.accsent) * 100 / eav.accsent mycalc (     select timestamp,       max(if(ref_type='s11s4createsessionreqrcvd', ref_data)) reqrcvd,      max(if(ref_type='s11s4createsessionrespaccsent', ref_data)) accsent     test.sgw_s5s11     timestamp = '2013-08-21 00:00:06'     group timestamp   ) eav;

ps: use single quotes ' string , date literals, not double quotes ". in standard sql, double quotes delimiting table , column identifiers. mysql treats 2 types of quotes same default, subject sql_mode , won't behave same way if ever use brand of rdbms. it's develop habit of using quotes in standard way.


Comments

Post a Comment

Popular posts from this blog

Line ending issue with Mercurial or Visual Studio -

-
i seem have strange issue caused either visual studio or mercurial. localised single project, i'm guessing in project configuration causing issue. at difficult specify point, when perform action in visual studio, update every line ending in given file, means when @ file in sourcetree, every line has changed. i can replicate using nuget add package dependency; packages.config entirely replaced. if commit changes, including line endings, later same issue occur. workaround have shelve changes , reapply them, whereupon lines written correctly. i'm not sure application blame here. did make mistake of allowing sourcetree change mercurial configuration, issue have fixed, i'm not sure if has persisted. i thought might issue mercurial.ini files or hgrcs don't seem contain untoward. here's mercurial.ini [ui] username = .... [auth] bb.prefix = https://bitbucket.org/ bb.username = .... bb.password = .... [extensions] mq = rebase = [web] allow_push = * push_s...
Read more

java - Jtable duplicate Rows -

-
Image
when add new rows in jtable duplicate rows many times this method update jtable public final void tableaucomande(){ ((defaulttablemodel)tabcommande.getmodel()).getdatavector().clear(); session s=hibernateutil.getsessionfactory().opensession(); criteria cr1= s.createcriteria(commande.class); list <commande> lcs=cr1.list() ; criteria cr2= s.createcriteria(lignecommande.class); list <lignecommande> lck=cr2.list() ; if(lcs.size()!=0 && lck.size()!=0) { (commande lc:lcs) { for(lignecommande lig:lck){ ((defaulttablemodel)tabcommande.getmodel()).addrow(new object[]{ lc.getid_cmd(),lc.getnumcmd(),lc.getdate_cmd(),lc.getclient().getnomprenom(), lig.getproduit().getlib_prd(),lig.getquantite(),lig.getproduit().getid_prd() }); } } // tabcommande.setrowselectioninterval(tabcommande.getrowcount()-1,tabcommande.getrowcount()-1); } } this code add add data rows displaye...
Read more

java - Run a .jar on Heroku -

-
i trying run .jar file on heroku, , can't find instructions anywhere on how this. have read wants me compile uploading heroku. possible, , if is, how this? if helps, trying run minecraft craftbukkit server, , don't have pom.xml file yet. you didn't provide details i'll try give , people reaches question way it. first of all, create heroku account , create heroku application (assumed have done that). second, need generate application jar , upload heroku. so, open terminal , install heroku cli (assuming unix terminal): $ sudo npm install -g heroku-cli you need have npm , nodejs installed, can check link: https://www.digitalocean.com/community/tutorials/how-to-install-node-js-on-ubuntu-16-04 once done that, can use npm update node version acceptable heroku-cli: $ sudo npm install -g n $ sudo n 7.10.0 and run: $ sudo npm install -g heroku-cli in order deploy jars, need install deploy plugin: $ sudo heroku plugins:install heroku-cli-deploy ...
Read more