sql - How to remove the column in group by -

-

i working on query want mg.actid appear select column not within group by because not listing results want. group structure getting spoiled if have in group by. please let me know how retain in select column , remove group by.

want group by sr.rsrcid, sr.frsntnm, sr.lstm

query :

select (sr.lstnm+ ', '+ sr.frstnm) 'employee name',  mg.actid goalid,  sr.rsrcid empid,  count(mg.actid) '# goals',  sum(case        when prntactid != '' 1        else 0      end) '# aligned'   tm.sresource sr      left outer join tm.matrixgoal mg           on sr.rsrcid = mg.rsrcid , mg.delflg = 0  mg.activeflg = 1 group  sr.rsrcid,sr.lstnm,sr.frstnm order sr.lstnm,sr.frstnm

one way use 'partition by' clause in aggregate functions (like sum , count), , select distinct rows data set. effectively, move group statement partition each aggregate, this:

select distinct (sr.lstnm+ ', '+ sr.frstnm) 'employee name',  mg.actid goalid,  sr.rsrcid empid,  count(mg.actid) on (partition sr.rsrcid,sr.lstnm,sr.frstnm) '# goals',  sum(case        when prntactid != '' 1        else 0      end) on (partition sr.rsrcid,sr.lstnm,sr.frstnm) '# aligned'   tm.sresource sr      left outer join tm.matrixgoal mg           on sr.rsrcid = mg.rsrcid , mg.delflg = 0  mg.activeflg = 1 order sr.lstnm,sr.frstnm

Comments

Post a Comment

Popular posts from this blog

apache - setting document root in antoher partition on ubuntu -

-
i place apache document root inside partition of ubuntu hard drive, keep getting forbidden message, when place home directory woking find, how be? group or owner affected ? here mysite.conf , apache2.conf when place document root in home folder (working) #site-available/mysite.conf documentroot /home/jono/www #/etc/apache2/apache2.conf <directory /home/jono/www/> options indexes followsymlinks allowoverride none require granted </directory> bu when change document root partition keep getting forbidden messasge #site-available/mysite.conf documentroot /media/jono/website_data/www #/etc/apache2/apache2.conf <directory /media/jono/website_data/www/> options indexes followsymlinks allowoverride none require granted </directory> is owner/group access affected ? or there problem ? at last it's working, have grant access www:data accesing whole directory @mark-b, using chown -r www-data:www-data whole directory is...
Read more

cytoscape.js - How to add nodes to Dagre layout with Cytoscape -

-
i trying add nodes dagre graph through cityscape.js. problem nodes not painted. code have: cy.add({ group: "nodes", data: { id:"n0",name:"n0",weight: 75 }, }); $.ajax(url).done(function(jsontree){ var newnodes=[]; for(var i=1;i<6;i++){ //var child = jsontree.result[i]; newnodes.push( { group: "nodes", data: { id: "n"+i, name:"n"+i}}, { group: "edges", data: { id: "e"+i, source: "n0", target: "n"+i } } ); } cy.add(newnodes); any ideas? have used loaded function reload graph not work. not know if have use specific function reload graph. thank in advance. you need add positions new nodes. cytoscape.js docs : it important note positions of newly added nodes must defined when calling cy.add(). nodes can not placed in graph without valid position — otherwise not displayed. yo...
Read more

Process 'command 'F:\android-sdk\build-tools\21.1.2\aapt.exe'' finished with non-zero exit value 1 -

-
here content of build.gradle file: // top-level build file can add configuration options common sub-projects/modules. buildscript { repositories { maven { credentials { username artifactoryusername password artifactorypassword } url 'http://test:8081/artifactory/libs-release-local' } mavencentral() } dependencies { classpath 'com.android.tools.build:gradle:1.1.0' } allprojects { repositories { maven { credentials { username artifactoryusername password artifactorypassword } url 'http://test:8081/artifactory/libs-release-local' } mavencentral() maven { url 'http://repo1.maven.org/maven2' } jcenter() } } here content of app\build.gradle : apply plugin: 'com.android.application' android { compilesdkversion 21 buildtoolsversion "21.1.2" lintoptions { abortonerror false ...
Read more