SQL count multiple cells as combination -


i have following sql table shows case number , value, case number appear 2 cases in group, want count how many combinations same case number appearing in table. ware order different, see case , c, both of them should count same combination. 

case  value      1992      1956  b     2000  b     2001  c     1956  c     1992

the goal total number of each combination, output format doesn't matter. 1 of expected result:

seq  value   frequency  1    1992    2  1    1956    2  2    2000    1  2    2001    1

what if there 3 cases combination?

this works number of values case. increment frequency count when cases have same number of values , each 1 have match, no matter in order.

create table #table1     ([case] varchar(1), [value] int) ;  insert #table1     ([case], [value]) values     ('a', 1992),    ('a', 1956),    ('a', 1997),    ('b', 2000),    ('b', 2001),    ('c', 1956),     ('c', 1992),     ('c', 1997),   /*('c',1993),*/    ('d', 2005),    ('d', 2008),    ('e', 1956),     ('e', 1992) ,    ('f', 1956),    ('f', 1992),        ('g', 1956),    ('g', 1992)  ;  --query select min(a.[case]) [case], [values], count(*) frequency (     select  t.[case],              stuff(                     (                         select  ',' + cast (t1.[value] varchar(20))                            #table1 t1                           t1.[case] = t.[case]                         order t1.[value]                          xml path('')                     ),1,1,'') [values]        #table1 t     group t.[case]     )a group [values] order [case]

result whith values sorted in ascending order

case	values	frequency  a	1956,1992,1997	2  b	2000,2001	1  d	2005,2008	1  e	1956,1992	3


Comments

Post a Comment

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

i've set bento/ubuntu-16.04 vagrant box using php 5.6, , getting following error quite few times when try , run shell script: "uncaught reflection exception: class domdocument not exist". i've tried adding following top of script (not together), each no success: "sudo apt-get install php5.6-xml", "sudo apt-get install php5-xml", "sudo apt-get install php-dom" i've tried resetting server "sudo service apache2 restart". does have suggestions?
Read more

vue.js - Create hooks for automated testing -

Image
qa guys complained can't automate frontend testing. because our html looks same outside. lazy hack did this <template> <div :role="$options.name"> ... </div> </template> <script> export default { name: 'vmcomponentname' } </script> which takes name script , applies html. in browser get: <div role="vmcomponentname"> ... </div> line :role="$options.name" goes in every component. is there dry er solutions? please share. additional details i'll explain in details looking for. imagine have vmusercreate form creates user. test case can create user . to automatically test without role, have use following selector: .wrapper > .wrapper > .wrapper > .submit-button the test extremely brittle (will break lot). if use roles can use [role="vmusercreateform"] .submit-button selector. many folds less brittle. so basically, i'm ...
Read more

Add new key value to json node in java -

i have json file : { "id":1, "name":"abc", "addressdetails": { "city":"newyork" } } i wanted add 1 more key-value ("pincode" : "414141") node 'addressdetails'. i tried using : objectmapper mapper = new objectmapper(); jsonnode root = mapper.readtree(new file("d://test.json")); objectnode node = mapper.createobjectnode(); node.with("addressdetails").put("pincode", "414141"); but it's not getting added, there way can ? you can try you should modify file path objectmapper mapper = new objectmapper(); objectnode nodes = mapper.readvalue(new file("d:\\test.txt"), objectnode.class); nodes.with("addressdetails").put("pincode", "414141"); mapper.writer().writevalue(new file("d:\\test.txt"), nodes);
Read more