sql - Merge two tables with same column names, add counters -

-

i have 2 tables same columns, first column name , second count. merge these tables, each name appears added count of 2 tables:

table1:           table2:            result table: name   count      name   count       name   count name1  1          name3  3           name1  1 name2  2          name4  4           name2  2 name3  3          name5  5           name3  6 name4  4          name6  6           name4  8                                      name5  5                                      name6  6

as of moment have created pretty ugly structure execute this, , would like know if possible results in more elegant way.

what have far (table1 test1 , table2 test2):

create table test1 ( name varchar(40), count integer); create table test2 ( name varchar(40), count integer); create table test3 ( name varchar(40), count integer); create table test4 ( name varchar(40), count integer); create table test5 ( name varchar(40), count integer);  insert test4 (name, count) select *  test1; insert test4 (name, count) select *  test2; insert test3 (name , count) select t1.name, t1.count + t2.count  test1 t1 inner join test2 t2 on t1.name = t2.name; select merge_db(name, count) test3; insert test5 (name, count) (select name, max(count) test4 group name);   create function merge_db(key varchar(40), data integer) returns void     $$ -- souce: http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql     begin         loop             -- first try update key             update test4 set count = data name = key;             if found                 return;             end if;-- not there, try insert key -- if else inserts same key concurrently,        -- unique-key failure             begin                 insert test4(name,count) values (key, data);                 return;             exception when unique_violation then-- nothing, , loop try update again             end;         end loop;     end;     $$     language plpgsql; 

=> create table t1 (name text,cnt int); => create table t2 (name text,cnt int); => insert t1 values  ('name1',1), ('name2',2), ('name3',3), ('name4',4); => insert t2 values  ('name3',3), ('name4',4), ('name5',5), ('name6',6); =>   select name,sum(cnt)  (select * t1  union  select * t2 ) x  group name  order 1;   name  | sum  -------+-----  name1 |   1  name2 |   2  name3 |   6  name4 |   8  name5 |   5  name6 |   6 (6 rows)

Comments

Post a Comment

Popular posts from this blog

c# - How to set Z index when using WPF DrawingContext? -

-
how set z-index drawing object when using drawingcontext.drawxxx() methods? the object drawn last have higher z index. can't change index of drawn objects. way draw in order. if using wpf (as placed tag), can use, example, canvas control. create shapes need like polyline obj = new polyline(); //... // ... set properties of obj and add them canvas uielementcollection: yourcanvasname.children.add(obj); //or yourcanvasname.children.insert(i, obj); first items of collection have higher z index. advantages in way: no need redraw on window changes, can anytime move objects , change order.
Read more

razor - Is this a bug in WebMatrix PageData? -

-
i think may have found bug in webmatrix's pagedata, not sure. concerns how pass data partial page calling page. in webmatrix documentation (tutorials, e.g. " 3 - creating consistent look ", , example code), pagedata recommended mechanism pass data between pages (e.g. content page layout page, or partial page). however have found not work other way, pass data partial page calling page. modifying or adding entries in pagedata in partial page, not seem calling page. cutting right down simplest possible example, in test page may have this: @{ pagedata["test"] = "initial entry"; } <p>before calling partial page, test value @pagedata["test"]</p> @renderpage("_testpartial.cshtml") <p>after returning calling page, test value @pagedata["test"]</p> and in _testpartial.cshtml page might have this: @{ pagedata["test"] = "modified entry"; } <p>in partial page...
Read more

visual c++ - Using relative values in array sorting ( asm ) -

-
i need sort through array , sort each individual row in array in ascending order. doesn't seem going (surprise!) keep getting hit 2 errors: a2101: cannot add 2 relocatable labels , a2026: constant expected here's sort, makes sense me, think i'm still trying implement high-lvl language techniqes assembly. there way around not being able use relative values? (the array 7 rows 9 columns, btw). mov cx, 7; cx = number of rows outer: ; outer loop walk through rows push cx mov cx, 9 mov row, cx ;rows middle: ; middle-loop walk through columns push cx sub cx, 1 ;cx = cx-1 mov column, cx ;columns inner: ;inner loop - compare , exchange column values cmp marray[row*9 + column], marray[row*9 + column+1] xchg marray[row*9 + column+1], marray[row*9 + column] ; compare , exchange values marray table inc column loop inner pop cx loop mid...
Read more