sql server - SQL Outer Join to show rows with no data -


need out data , im stuck in join swamp. run on ms sql server.

i have list of clients in 1 table (clientgroup)

clientgroup groupid | clientid 1       | 11 1       | 12 1       | 13 1       | 14 1       | 15

and have table have if clientid has clientactivity

clientcontactlog logid | clientid 1     | 11 2     | 14 3     | 15 4     | 11 5     | 11 6     | 11

then have table info clientactivity

contactlog logid | logtype | logdate      | logtext 1     | 1       | ’2016-05-16’ | ’toys’ 2     | 1       | ’2016-05-16’ | ’toys’ 3     | 1       | ’2016-05-16’ | ’toys’ 4     | 2       | ’2016-05-17’ | ’lunch’ 5     | 2       | ’2016-05-18’ | ’dinner on mars’ 6     | 1       ! ’2016-05-19’ | ’dinner on mars’

i want make mothly statistic (sum on logtypes in contactlog) , include clients have in client list id 1. output shows clients have no record clientcontactlog. need with.. data have input need show 0 on clients has no record.

output should be

clientname | sum(logtype1) client11   | 2 client12   | 0 client13   | 0 client14   | 1 client15   | 1

thanks input , help

pretty sure mean count(logtype), not sum(logtype), since summing date makes no sense whatsoever. in case, can asking for.

select clientname, isnull(ct, 0) logcount clientgroup cg inner join clients c     on cg.clientid = c.clientid left join (     select logid,          count(*) ct     clientcontactlog     group clientid     ) cl

notes:

  • i assumed clients table containing client name.
  • i not looking @ contactlog table because not needed provide requesting.

Comments

Post a Comment

Popular posts from this blog

Command prompt result in label. Python 2.7 -

i want put result(output) of shell command label. command works, label shows "0", however, in command prompt, result shown correctly, need showed in label. doing ubuntu. myg1 = button(root, text="rodyti informacija", command=lambda: gauti()) myg1.pack(side=bottom) def gauti(): imti = tekstas.get("1.0", "end-1c") info = subprocess.call("id '{imti}' ".format(imti=imti), shell=true) w = label(root, text= info) w.pack(side = bottom) subprocess.call() returns exit code of process created (in case 0 success). if want text output of process, should instead call subprocess.check_output() .
Read more

javascript - How do I use URL parameters to change link href on page? -

i change href value of link using url parameters. example: default <a id="dlink" href="link-1">link</a> but, when user goes https://url.com/?dlink=link-2 it swaps out link new link <a id="dlink" href="link-2">link</a> first need value url. if you're not using javascript framework can create function using jquery. please check get url parameter jquery or how query string values in js it. after that, element "a" id , set value there. $("#dlink").attr("href", hreffromparam);
Read more

amazon web services - AWS Route53 Trying To Get Site To Resolve To www -

so have domain nameservers pointed aws route 53. site hosted on ec2 instance , reachable resolves site.com, if input www.site.com. i'm curious how site resolve www.site.com. have under route53 domains hosted zone 2 additional record sets outside of ns , soa: a site.com ec2 elastic ip cname www.site.com ec2 public dns i can reach site using both site.com , www.site.com, resolve site.com. there way make if enter either, resolves www.site.com? at first, both should record set. a site.com ec2 elastic ip www.site.com ec2 elastic ip and need redirect setting in web server (nginx, apache, ..) following. (source: how redirect www non-www nginx on centos 7 ) redirect non-www www if want redirect users plain, non-www domain www domain, add server block: server { server_name example.com; return 301 $scheme://www.example.com$request_uri; } save , exit. configures nginx redirect requests "example.com" "www.example.com". not...
Read more