sql server - Counting occurrences for each row and update to new column -


i have table: enter image description here

i need count occurrence of same line in column closetime , write number of occurrences in last column count clause item , same account.

my single select concrete item , account 

select closetime, count(*) closetime statement account = 2013169 , item = 'eurusd' group closetime having count(*) > 1 order count(*) desc

it possible count in 1 update query?

depending on use case, not idea. better off creating "count" calculated column or leaving off base table entirely , creating view included count column. also, avoid trying have column name reserved word, count.

however, if want it, can in 1 query. make sure on same page, belive trying group closetime , account , item not included in group (so, counted if same close time , different item), want able update target account , item.

in case, like

with cte1 (select closetime, count(*) numcount dbo.[statement] group closetime) update dbo.[statement] set [count] = cte1.numcount dbo.[statement] sd join cte1 on sd.closetime = cte1.closetime sd.account = <your account num> , sd.item = <your item>

edit:

if understand comments want correctly, use query like:

with cte1 (select closetime, count(*) numcount dbo.[statement] group closetime) select sd.*, --i advise not using * in production cte1.numcount [count] statement sd join cte1 on sd.closetime = cte1.closetime sd.account = <your account num> , sd.item = <your item>

while use cte, single sql statement , display counts selected account num , item closetime.


Comments

Post a Comment

Popular posts from this blog

javascript - backbone.js Collection.add() doesn't `construct` (`initialize`) an object -

<!doctype html public "-//w3c//dtd xhtml 1.0 strict//en" "http://www.w3.org/tr/xhtml1/dtd/xhtml1-strict.dtd"> <html> <head> <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script> <script src="http://documentcloud.github.com/underscore/underscore-min.js"></script> <script src="http://documentcloud.github.com/backbone/backbone-min.js"></script> </head> <body> <button id="cmd_create_event" name="cmd_create_event" type="button">create new `event`</button> <script type="text/javascript"> var eventmodel = backbone.model.extend({ initialize : function() { console.log("`event` initialized. id: " + this.cid); this.bind("change:status", function() { console.log(this.get("status") + " value `status`"); }); this.bind("error", function(model, e...
Read more

c++ - Accessing inactive union member and undefined behavior? -

i under impression accessing union member other last 1 set ub, can't seem find solid reference (other answers claiming it's ub without support standard). so, undefined behavior? the confusion c explicitly permits type-punning through union, whereas c++ ( c++11 ) has no such permission. c11 6.5.2.3 structure , union members 95) if member used read contents of union object not same member last used store value in object, appropriate part of object representation of value reinterpreted object representation in new type described in 6.2.6 (a process called ‘‘type punning’’). might trap representation. the situation c++: c++11 9.5 unions [class.union] in union, @ 1 of non-static data members can active @ time, is, value of @ 1 of non-static data members can stored in union @ time. c++ later has language permitting use of unions containing struct s common initial sequences; doesn't permit type-punning. to determine whether union type-punning is allowe...
Read more

php - Get uncommon values from two or more arrays -

is there function in php give array of uncommon values 2 or more arrays? for example: $array1 = array( "green", "red", "blue"); $array2 = array( "green", "yellow", "red"); .... $result = function_needed($array1, $array2,...); print_r($result); should give output: array("blue", "yellow", ...); use array_diff , array_merge : $result = array_merge(array_diff($array1, $array2), array_diff($array2, $array1)); here's demo. for multiple arrays, combine callback , array_reduce : function unique(&$a, $b) { return $a ? array_merge(array_diff($a, $b), array_diff($b, $a)) : $b; } $arrays = array( array('green', 'red', 'blue'), array('green', 'yellow', 'red') ); $result = array_reduce($arrays, 'unique'); and here's demo of that.
Read more