sql - Count percentage of certain column values and add it to a new column -


i have following table 

[table_orangeisnewblack] id  |   name    |   color   |   redpercent ------------------------------------------ 1   |   donald  |   orange  |    2   |   hillary |   white   |    3   |   barack  |   black   |    4   |   bernie  |   grey    |    1   |   donald  |   red     |    2   |   hillary |   red     |    3   |   barack  |   black   |    4   |   bernie  |   grey    |    1   |   donald  |   red     |    2   |   hillary |   blue    |    3   |   barack  |   red     |    4   |   bernie  |   purple  |

i need add percentage value presenting how person 'red'

donald's record 

1       donald      orange       1       donald      red          1       donald      red

redpercent : (2 / 3 ) * 100 = 66,66

hillary

2       hillary     white        2       hillary     red          2       hillary     blue

'blue' , 'purple' not count, :

redpercent : (1 / 2) * 100 = 50,00

barack

3       barack      black        3       barack      black        3       barack      red

redpercent : (1 / 3) * 100 = 33,33

bernie

4       bernie      grey         4       bernie      blue         4       bernie      purple

'blue' , 'purple' not count

redpercent : (0 / 1) * 100 = 00,00

result needed: 

[table_orangeisnewblack] id  |   name    |   color   |   redpercent ------------------------------------------ 1   |   donald  |   orange  |   66,66 2   |   hillary |   white   |   50,00 3   |   barack  |   black   |   33,33 4   |   bernie  |   grey    |   00,00 1   |   donald  |   red     |   66,66 2   |   hillary |   red     |   50,00 3   |   barack  |   black   |   33,33 4   |   bernie  |   grey    |   00,00 1   |   donald  |   red     |   66,66 2   |   hillary |   blue    |   50,00 3   |   barack  |   red     |   33,33 4   |   bernie  |   purple  |   00,00

i've tried :

;with cte     (         select  id,                      -- division: count of reds  / count of persons rows                      ((select count(id) table_orangeisnewblack                     color = 'red' )                     /                            (select count(id) table_orangeisnewblack                     ( color  <> 'blue' ,                             color  <> 'purple' ))                     )                     redprcnt                 -- end of division         table_orangeisnewblack     )     update t1         set redpercent = cte.redprcnt         table_orangeisnewblack t1      inner join cte on(t1.id = cte.id)

select clause...

select  id,              -- division: count of reds  / count of persons rows              ((select count(id) table_orangeisnewblack             color = 'red' )             /                    (select count(id) table_orangeisnewblack             ( color  <> 'blue' ,                     color  <> 'purple' ))             )             redprcnt         -- end of division table_orangeisnewblack

...gives zeros redprcnt

should use group @ point?

any suggestions?

thanks in advance.

count results in integer, count/count integer division truncates , base query tries calculate overall percentage instead of % per group.

additionally calculation can simplified using conditional aggregation: 

select id,         -- division: count of reds  / count of persons rows     100.00 * count(case when color = 'red' id end)           / nullif(count(case when color  <> 'blue' , color  <> 'purple'  id end), 0) redprcnt table_orangeisnewblack group id

the case returns null non-matching rows not counted.

edit:

if need repeat percentage each row can switch windowed aggregate instead:

select id, name, color,     100.00 * count(case when color = 'red' id end) over(partition name)           / nullif(count(case when color  <> 'blue' , color  <> 'purple'  id end) over(partition name), 0) redprcnt  table_orangeisnewblack

Comments

Post a Comment

Popular posts from this blog

asynchronous - C# WinSCP .NET assembly: How to upload multiple files asynchronously -

i using winscp .net assembly uploading files. want upload multiple files asynchronous way. have created method works single upload. public class uploadingdata { private sessionoptions _sessionoptions; private session _session; //connection etc private void onconnected() { foreach (var fileinfo in localfileslist) { var task = task.factory.startnew(() => uploadfilesasync(fileinfo)); } } private async task uploadfilesasync(string file) { string remotefilepath = _session.translatelocalpathtoremote(file, @"/", "/test_data_upload"); var uploading = _session.putfiles(file, remotefilepath, false); //when done await task.run(() => thread.sleep(1000)); } } please suggest me correct way. thanks you can use backgroundworker class this: string[] images = new string[50]; foreach (var path in images) { backgroundworker
Read more

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

i getting "__type": "com.amazon.coral.service#serializationexception" as reply in postman & in test console in api gateway trying post record directly dynamodb using api proxy services.. referring aws article - https://aws.amazon.com/blogs/compute/using-amazon-api-gateway-as-a-proxy-for-dynamodb/ here's mapping { "tablename": "tablenamegoeshere", "item": { "id" : "$context.requestid" "eventname" : "$input.path('$.eventname')", "timestamp" : $input.path('$.timestamp'), "answers": "$util.parsejson($input.path('$.answers'))" } } update: did asked ... , worked if try add array of json objects gives me above same error - here's trying now. please - couldnt find on google well #set($inputroot = $input.path('$')) { "tablename": "answer", "item": {
Read more

asp.net - Problems sending emails from forum -

i have been redesigning site has message board, has been written guy looked after site. it has few problems , written in .asp , uses .mdb files database. you can see whole thing here . feel free post if helps, test thing have set using original files , new database. the basic problem when posts message, supposed email sent them, have posted it. when replies, originator supposed message, replier has been posted. i’d have thought @ point in process there should script/file in postmessage.asp? the postmessage.asp page below. <% option explicit %> <!-- #include virtual="common/adovbs.inc" --> <html> <head> <meta http-equiv="content-type" content="text/html; charset=windows-1252"> <meta name="generator" content="microsoft frontpage 4.0"> <meta name="progid" content="frontpage.editor.document"> <title>new page 2</title> <base target="_self"
Read more