join - Split multiple count using in mysql with same table -


i have query in below format:

select etm.etm_taxonomy, count( pe.pp_profileid ) total_counts expertise_taxonomymaster etm left join expertise_taxonomy et on etm.etm_id = et.`et_taxonomy` left join expertise e on e.et_taxonomy = et.`et_id` left join profile_expertise pe on pe.pp_expertiseid = e.et_id pe.pp_profileid in (             select pj.pj_profileid         jobtitle_taxonomymaster jtm         left join jobtitle_taxonomy jt on jtm.jtm_id = jt.`jt_taxonomy`         left join jobtitle j on j.jt_taxonomy = jt.`jt_id`         left join profile_jobtitle pj on pj.pj_jobtitleid = j.jt_id         jtm.jtm_taxonomy = 'associate'             or jtm.jtm_taxonomy = 'partner'          )     , et_lawfirmid in (195,196) group etm_taxonomy

and have results follows: 

etm_taxonomy    total_counts advertising       18 antitrust         47     banking          258

but need below results, count should split based on jtm.ttm_taxonomy field 

etm_taxonomy    patners195  partners196     associates195   associates196 advertising       18          18              18              18 antitrust         47          47              47              47 banking          258          258            258              258

try way:

 select etm.etm_taxonomy,     sum (case when pj_tax.jtm_taxonomy = 'associate' 1 else 0 end) associates,    sum (case when pj_tax.jtm_taxonomy = 'partner' 1 else 0 end) partners,    sum (case when pj_tax.jtm_taxonomy = 'consultant' 1 else 0 end) consultants,    sum (case when pj_tax.jtm_taxonomy = 'counsel' 1 else 0 end) counsels,  count(pe.pp_profileid ) total_counts expertise_taxonomymaster etm left join expertise_taxonomy et on etm.etm_id = et.`et_taxonomy` left join expertise e on e.et_taxonomy = et.`et_id` left join profile_expertise pe on pe.pp_expertiseid = e.et_id inner join  ( select distinct pj.pj_profileid,jtm.jtm_taxonomy jobtitle_taxonomymaster jtm left join jobtitle_taxonomy jt on jtm.jtm_id = jt.`jt_taxonomy` left join jobtitle j on j.jt_taxonomy = jt.`jt_id` left join profile_jobtitle pj on pj.pj_jobtitleid = j.jt_id jtm.jtm_taxonomy = 'associate' or jtm.jtm_taxonomy = 'partner' or jtm.jtm_taxonomy = 'consultant' or jtm.jtm_taxonomy = 'counsel' ) pj_tax on  pe.pp_profileid= pj_tax.pj_profileid et_lawfirmid =195 group etm_taxonomy

-

Comments

Post a Comment

Popular posts from this blog

css - SVG using textPath a symbol not rendering in Firefox -

using textpath within symbol doesn't render in firefox. renders fine in latest chrome , ie, when try reference symbol, svg text doesn't render in firefox (37.0.1) - first box appears empty. code below (no external dependencies), there should 2 boxes word test flowing vertically in centre of each. edit: thought issue somehow involved flexbox layout, until paul pointed out issue exists without flexbox layout. the html is: <div> <svg id="not_working" viewbox="0 0 250 1200" preserveaspectratio="xmidymid meet"> <symbol id="test_symbol1" preserveaspectratio="xmidymid meet" viewbox="0 0 250 1200"> <path id="test_symbol_path" d="m 100 1200 l 100 0" /> <text font-size="100" fill="red"> <textpath text-anchor="middle" startoffset="50%" xlink:href="#test_symbol_path...
Read more

Java 8 + Maven Javadoc plugin: Error fetching URL -

i attempting generate javadoc links javadoc dependencies. have tried various means generate javadoc not produce qualified class names references classes dependencies. wanted links java doc simplified class names. however, java api classnames, no links , have qualified class names. working java 8. have following configuration: <plugin> <groupid>org.apache.maven.plugins</groupid> <artifactid>maven-javadoc-plugin</artifactid> <version>2.10.2</version> <configuration> <reportoutputdirectory>${project.basedir}/target</reportoutputdirectory> <destdir>javadoc</destdir> <windowtitle>epiphany</windowtitle> <doctitle>epiphany</doctitle> <show>private</show> <detectlinks>false</detectlinks> <detectoffline...
Read more

datatable - Matlab struct computations -

i download data yahoo! finance with: data = getyahoodailydata({'msft', 'axp'},'01/01/2000', '01/01/2015', 'dd/mm/yyyy'); and data stored 1x1 struct. want create tx2 matrix of daily adjusted closing prices msft , axp, column 7 in each table in struct. how can that? or better : there way make computations directly on information/prices in struct? you can access data in struct name. vecmsftadj = data.msft.adjclose; vecaxpadj = data.axp.adjclose; % if want n x 2 matrix madjclose = [vecmsftadj, vecaxpadj]; % personnaly prefer working table tadjclose = table; tadjclose.msftadj = vecmsftadj; tadjclose.axpadj = vecaxpadj;
Read more