SQL Server 2012 Query Multiple but not all Column Counts -


i'm building query application in sql server 2012. query supposed grab sorts of statuses single table sql server.

tables structured this:

projecttype | team | projectstatus  ----------------------------------  proj1          b      rec  proj1          b      rec  proj1          b      hold  proj2          b      rec  proj3               hold  proj4          c

my wanted output be:

projecttype | total | team | rec| hold | | -----------------------------------------------   proj1         3      b      2     1      0   proj2         1      b      1     0      0   proj3         1           0     1      0   proj4         1      c      0     0      1

i think possible because know statuses ever be:

all statuses 

rec, ba, dq, p, prev, pred, pcom, 90, 90rev, 90red, 90com, ss, ssred, d, c

what have tried far:

select      projecttype, team, count(projectstatus)       sites       (projecttype not null , projecttype <> '') , team <> ''  group      projecttype, team    select      s.projecttype, s.team, s.projecttype, c.cnt      sites s inner join      (select projecttype, count(projectstatus) cnt       sites       projectstatus = 'rec' group projecttype) c on s.projecttype = c.projecttype

here thought add

inner join ( select projecttype, count(projectstatus) cnt sites  projectstatus='rec' group projecttype) c  on s.projecttype = c.projecttype

per status have in order count them individually...

edit: output wrong counted same projecttype different ones ( had 10 diff ones ) displayed same ones 10 times over

is there better way of doing - can me complete?

based on question, seems have multiple statuses (all known) , want show counts status -- placing statuses across top. there ways using pivot, i'm not @ computer dev resources right now, how (out of head -- untested).

select    projecttype,   count(*) total,   team,   sum(case when projectstatus = 'rec' 1 else 0 end) 'rec',   sum(case when projectstatus = 'hold' 1 else 0 end) 'hold',   sum(case when projectstatus = 'some' 1 else 0 end) 'some'   sites   projectstatus in ('rec', 'hold', 'some') group   projecttype,   team

sqlfiddle


Comments

Post a Comment

Popular posts from this blog

curl - PHP fsockopen help required -

i trying work on cloudflare api , have found in documentation. unfortunately have no idea how can use in fsockopen or through curl. here example of post request. need know how can make request below data post using curl or fsockopen curl https://www.cloudflare.com/api_json.html -d 'a=cache_lvl' \ -d 'tkn=8afbe6dea02407989af4dd4c97bb6e25' \ -d 'email=sample@example.com' \ -d 'z=example.com' \ -d 'v=agg' here link cloudflare https://www.cloudflare.com/docs/client-api.html#s4.2 try one: $ch = curl_init("https://www.cloudflare.com/api_json.html"); curl_setopt($ch, curlopt_returntransfer,1); curl_setopt($ch, curlopt_followlocation, 1); curl_setopt($ch, curlopt_ssl_verifypeer,false); // handling of -d curl parameter here. $param = array( 'a' => 'cache_lvl', 'tkn' => '8afbe6dea02407989af4dd4c97bb6e25', 'email' => 'sample@example.com', 'z' =...
Read more

HTTP/1.0 407 Proxy Authentication Required PHP -

i working in restricted network/limited access internet. since have use proxy make web service call. here php code calling web service , response . $client = new soapclient("http: //www.xyz.com/abc/wsdls/login.wsdl", array("trace" => 1, "exceptions" => 1, 'proxy_host' => $proxy_host, 'proxy_port' => $proxy_port, 'proxy_login' => $proxy_login, 'proxy_password' => $proxy_password)); response is fatal error: uncaught soapfault exception: [wsdl] soap-error: parsing wsdl: couldn't load ' http://www.xyz.com/abc/wsdls/login.wsdl ' : failed load external entity if copy url error message , paste in browser(with same proxy settings) works. 2nd scenario is, if pass values instead of variables giving me diffarant response $client = new soapclient("http: //www.xyz.com/abc/wsdls/login.wsdl", array("trace" => 1, "exceptions" => 1, 'proxy_host' ...
Read more

c# - Resource not found error -

i have problem accessing method on homecontroller. show code of method : [httpget] public actionresult decriptidentifiantetredirige(string login_crypter, string mdp_crypter) { string loginacrypter = _globalmanager.protegemotdepasse(login_crypter); string mdpacrypter = _globalmanager.protegemotdepasse(mdp_crypter); user userapp = new models.user(login_crypter, mdp_crypter); if (userapp.authentificationvalidee(userapp.userlogin, userapp.password)) { session["name"] = userapp.userlogin; return redirecttoaction("accueil", "home"); } else { return redirecttoaction("validerauthentification", "home"); } } then in routeconfig.cs wrote route : routes.maproute( name: "authentificationapresdecryptage", url: "{controller}/{action}/{login_crypter}/{mdp_crypter}", defaults: new...
Read more