sql - ms access combining rows -



sql - ms access combining rows -

this seems should simple solution reason cannot wrap head around this.

i have table has user_full_name primary key , 15 columns of various metrics right. have instances multiple user_full_name's spelled incorrectly should in fact summed same row. ie. "david hands" spelt "davide handes" , "bobby orr" spelt "boby or." there 150 rows total in table there 136 unique user_full_name.

how can write sql query merge 150 rows unique 136 rows user_full_name should combined in fact combined??

i have added column matrix table called tps_user_names, 1 user_full_name , 1 duplicate_user_full_name. query i've come not seem work:

select tun.user_full_name, sum(ad.processed_mss) all_data advertisement left bring together tps_user_names tun on ad.user_full_name = tun.user_full_name , ad.user_full_name = tun.duplicate_user_full_name ad.user_full_name = 'a' , ad.user_full_name = 'a1' grouping tun.user_full_name

not strictly coding solution, create matrix table has 2 columns, duplicated user_full_names , actual user_full_name, left bring together table , utilize actual user_full_name whenever aggregations. short of revisiting schema or correcting errors in table itself, best way, imo. in case keeps happening, add together row matrix table instead of changing code. (it should noted can utilize interim step involving matrix table re-normalize original table. key relationship issues aside, can create matrix, pull aggregated query, utilize reinsert into/update original table.)

edit: have supplied code, here modifications can create integrate new table mix:

select nz(tun.user_full_name, ad.user_full_name) user_full_name_agg, sum(ad.processed_mss) all_data advertisement left bring together tps_user_names tun on ad.user_full_name = tun.duplicate_user_full_name grouping nz(tun.user_full_name, ad.user_full_name);

the nz select first non-null value tun.user_full_name or ad.user_full_name finds. dupes, utilize consolidated user_full_name finds in tun, others, 1 in ad.

sql ms-access sum

Comments

Post a Comment

Popular posts from this blog

php - Android app custom user registration and login with cookie using facebook sdk -

php - Android app custom user registration and login with cookie using facebook sdk - i developing android application in users need register , log in using facebook. approximately, flow. open app first time, authenticate facebook, data, send info server create them business relationship in application, start session new business relationship in server, cookie perform later interactions. so far good. what need know wich info have send server, can able identify users when log in seccond time. because need start session in server , send cookie android app farther interactions. i'm using php server side language. i thinking getting getaccesstoken() in android facebook sdk, pass server on business relationship creation , storing in android app. but can't manage how login users on sec time utilize app. any suggestions ? in advance ! to uniquely identify user within app, should create request /me , user's id. guaranteed unique , remain con
Read more

django - Access session in user model .save() -

django - Access session in user model .save() - is possible access current session in user model .save()? pseudo code of want achieve: # users.models.py def save(self, *args, **kwargs): created = true if self.pk: created = false super(abstractuser, self).save(*args, **kwargs) # post-save if created: look_for_invite_in_session_and_register_if_found(self, session) seems wrong in architecture. shouldn't access request in models layer. work request must done in view. can this: user, created = abstractuser.objects.get_or_create(name=name) if created: look_for_invite_in_session_and_register_if_found(user, request.session) django session
Read more

php - .htaccess Multiple Rewrite Rules / Prioritizing -

php - .htaccess Multiple Rewrite Rules / Prioritizing - here current htaccess file: rewriteengine on rewritecond %{http_host} !^www\.website\.com [nc] rewritecond %{http_host} ([^.]+)\.website\.com [nc] rewriterule ^(.*)$ http://www.website.com/gotourl.php?urlid=?%1 [l] rewriterule ^(\w+)$ ./gotourl.php?urlid=$1 [nc,l] rewriterule ^(\w+)/$ ./gotourl.php?urlid=$1 [nc,l] the website in question url shortener i'm working on testing/developing , personal use. above code may not ideal i'm not familiar .htaccess , made via googling , testing. want url shortening aspect: http://website.com/1234 -> http://website.com/gotourl.php?urlid=1234 http://website.com/1234/ -> http://website.com/gotourl.php?urlid=1234 the problem cannot figure out how ignore directories. want specific directories go specific pages: http://website.com/img (or) http://website.com/img/ -> http://website.com/img.php http://website.com/img/1234 (or) http://website.com/
Read more