In Standard SQL you would use analytic functions for this. They are not available in every DBMS, however. select itemid, supplier, cost, saleprice, count(*) over (partition by itemid) as count_itemid, count(*) over (partition by supplier) as count_supplier, sum(cost) over (partition by supplier) as cost_suppler from countifexpirement; Here is the same...

ms-access,sum,ms-access-2010,ms-access-2013,running-total

Since the field name includes spaces, bracket it like this: [Week Ending Date] Assuming it's Date/Time type, use # delimiters before and after the date value. Finally I think you want to get the sum from rows where [Week Ending Date] <= the date in the current row. DSum("[L2N]","Occupied Apts...

sql,sql-server,sum,subquery,aggregate-functions

I think it would be better to move the subquery to the from clause. Based on your logic, you are looking for joins, not left joins -- after all, the having clause is simply looking for any match. I think the following query does what you want: SELECT S.id, COUNT(dd.number)...

This is a good question, and one that does not have a good answer. The treatment of NULL in your two examples is different. The fundamental problem is what NULL means. Commonly, it is used to denote missing values. However, in the ANSI standard, it stands for an unknown value....

mysql,group-by,sum,sql-order-by,sql-limit

Using one of the answers from ROW_NUMBER() in MySQL for row counts, and then modifying to get the top. SELECT ParticipantId, SUM(Points) FROM ( SELECT a.participantid, a.points, a.id, count(*) as row_number FROM scores a JOIN scores b ON a.participantid = b.participantid AND cast(concat(a.points,'.', a.id) as decimal) <= cast(concat(b.points,'.', b.id) as...

java,function,recursion,sum,digit

You aren't actually recursing. You've setup your base case, but when solving a problem recursively you should have code within the function that calls the function itself. So you're missing a line that calls sumDigits, with a smaller argument. Consider two important parts of a recursive soutlion: The base case....

python-2.7,pandas,sum,dataframes,calculated-columns

That's what you need to do: df.add(df.A, axis=0) Example: >>> df = pd.DataFrame(np.random.rand(5,3),columns=['A','B','C']) >>> col_0 = df.columns.tolist()[0] >>> print df A B C 0 0.502962 0.093555 0.854267 1 0.165805 0.263960 0.353374 2 0.386777 0.143079 0.063389 3 0.639575 0.269359 0.681811 4 0.874487 0.992425 0.660696 >>> df = df.add(df.col_0, axis=0) >>> print...

fastestSum(new long[]{+1, -1}) => -18446744073709551616 ...

Sample data: colnm <- "squash" df <- data.frame(squash=1:10, avocado="so good") If you attach your df you could do this: attach(df) sum(get(colnm)) #[1] 55 This will do it without having to attach: sum(df[colnm]) #[1] 55 ...

I think you are looking for this: SELECT [Location Code], [Bill No_], SUM(Price) AS Price FROM (SELECT DISTINCT [Location Code], [Bill No_] , Price from [Item Ledger Entry] WHERE [Location Code] = '0001' and [Document Date] = '01.04.2015') t GROUP BY [Location Code], [Bill No_] ...

The calculation you are making is basically a convolution and convolution in the time domain is simply multiplication in the frequency domain.So just get the FFT of a and multiply it with itself, then perform an IFFT to return to the time domain.So in short, you can calculate b by...

mysql,reporting-services,sum,aggregate-functions

On the face of it the solution to what you're asking is very simple, just set the query in your dataset to be something like: SELECT Material, SUM(Amount) AS Sum FROM <table> GROUP BY Material ORDER BY Material That should create a dataset with two fields, Material and Sum, and...

One way with the dplyr package would be the following. Your data frame is called mydf. library(dplyr) group_by(mydf, Patient, Chrom) %>% mutate(whatever = sum(ProbeCount)) #Source: local data frame [8 x 6] #Groups: Patient, Chrom # # Patient Chrom Start End ProbeCount whatever #1 1 1 51599 62640 8 8684 #2...

Normally these things are done with powers of 2... The first checkbox has a "weight" of 1, the second one has a "weight" of 2, the third one a "weight" of 4 and so on. You can check if a checkbox is checked by doing if ((sum & 1) !=...

You can do this easily by using an absolute reference for the starting point of a SUM and using a relative reference for the end point. When copied down, this formulas works fine. =IF(SUM($A$1:A1)<$D$1,"x","") Results ...

You only need a single loop, which would take O(n). You need to iterate over the array once and count the number of even numbers and the number of odd numbers. Your output is evenCount + oddCount if oddCount is even, and evenCount + oddCount - 1 otherwise. The reason...

sql,sql-server,sum,aggregate-functions,exp

Oh, this is a pain. Most databases do not support a product aggregation function. You can emulate it with logs and powers. So, something like this might work: select t.*, (select exp(sum(log(serial))) from table t2 where t2.no <= t.no ) as cumeProduct from table t; Note that log() might be...

Using least would be much easier: SELECT LEAST(SUM(my_field), 86400) FROM my_table ...

When you read a number in from a CSV file, it reads in as a string, unless you do the following: reader = csv.DictReader(csvfile, quoting=csv.QUOTE_NONNUMERIC) This has bitten me a few times. The answer is either to create your reader as csv.QUOTE_NONNUMERIC, or to explicitly convert from str to int...

The below is your own answer, but rewritten such that it is more useful to others (self contained). I have also changed the %in% to < which should be much more efficient. library(raster) r <- raster(nr=100, nc=100) r[] = sample(ncell(r)) rs <- sort(as.vector(r), decreasing=TRUE) r_10m <- min( rs[cumsum(rs) < 10000000]...

python,indexing,pandas,sum,dataframes

Here is one way to avoid loop. import pandas as pd your_df = pd.DataFrame({'Col1':[1,2,3,4], 'Col2':[5,6,7,8]}) def your_func(df, column, cutoff): # do cumsum and flip over x = df[column][::-1].cumsum()[::-1] df[column][df.index > cutoff] = x[x.index > cutoff] return df # to use it your_func(your_df, column='Col1', cutoff=1) Out[68]: Col1 Col2 0 1 5...

Functions aren't called with parentheses and commas, unless you're pretending by using tuples. Functions are called how they're declared, with only whitespace between arguments: solve l r a b = [sumfunc l r a b] sumfunc l r a b = sum [0.0001 | x <- [1000 * l .....

c++,arrays,function,for-loop,sum

Try going through your code and thinking about the values of each variable at each step. In particular, look at sum. float sum = 0; for(int j=0; j < heliostatNumber; j++) { sum = sum + CosineEffect(SunRay[i], ReflectedRay[j]); } Cosine[i] = sum; ...

You may extend your WHERE clause to include other criteria using AND. And if you are going to SUM, you should use GROUP BY. SELECT SUM(prix) from notes_de_frais WHERE `date` BETWEEN '2015-05-01' AND '2015-06-06' AND `name`='Mr. X' AND `type`='plane tickets' GROUP BY `name`; ...

You can do this with: sum(df$col[1:997] * df$col[4:1000]) This will be a good deal quicker than looping through the indices and individually multiplying....

You want a group by rather than conditional aggregation. The query you want is something like this: SELECT (CASE WHEN b.BILL_DATE >= DateAdd(month,-1, GetDate()) THEN '<1' WHEN b.BILL_DATE >= DateAdd(month, -3, GetDate()) THEN '1-3' WHEN b.BILL_DATE >= DateAdd(month, -6, GetDate()) THEN '3-6' ELSE '6+' END) as AgedPeriod, SUM(Outstanding) FROM dbo.Tables...

matlab,function,matrix,plot,sum

Are you trying to do this?. I am not optimizing anything, for you to understand it clearly.... function test(zi) syms z x y n m fn fnm; f0=symfun(z.*x + 2.*y + exp(n) - sqrt(m),[z x y n m]); z=zi; ni=0:2:10; mi=0:2:10; fn=0; for i=1:length(ni); fn=symfun(fn+f0(z,x,y,ni(i),m),[x y m]); end fnm=0; for...

For the sum up: select mday, (select SUM(miles) from mm s where s.mday <= mm.mday) tot_miles from mm; For the 5 biggest mileage per day select mday, miles from mm order by miles desc limit 5 Change the column names and table name to match yours. Since you added the...

If I were you, I'd just do a simple sum in PHP of all the values you need summed and echo that out (I didn't follow which you needed summed so I gave you an example). I always recommend that, if possible, you should have PHP do the work and...

To me, I think you should change the sheet layout a little, create a User Defined Function (UDF) and alter the formulas in your Sum rows for efficient row/column adding (to make use of Excel's formula fill). The only issue is that you need to save this as a Macro-Enabled...

You need something Like this SELECT (SUM(IF(year <= 2015 AND year >= 2013,value,0) ) - SUM(IF(year <= 2014 and year >= 2013,value,0) )) AS total_sum FROM your_table_name GROUP BY Name This will do the trick...

Single liner in data.table: dt1[, lapply(.SD,sum), by=list(year,group)][, if (sum(amt) > 100) .SD, by=group] # group year amt #1: a 2001 60 #2: a 2002 65 ...

sql,postgresql,sum,aggregate-functions,subtract

I interpret your remark but that result can't to be negative as requirement to return 0 instead of negative results. The simple solution is GREATEST(): SELECT GREATEST(sum(amount) - (SELECT sum(amount) FROM solicitude WHERE status_id = 1 AND user_id = 1), 0) AS total FROM contribution WHERE user_id = 1; Otherwise,...

In your query, you are adding minute to minute, you can add minute to a date if you want to use TIMESTAMPADD function Try this one SELECT * from table WHERE TIMESTAMPDIFF(MINUTE,first_date,second_date) + TIMESTAMPDIFF(MINUTE,fourth_date,third_date) > 120 ...

You can try f1 <- function(x) if(all(is.na(x))) NA_integer_ else sum(x, na.rm=TRUE) aggregate(.~name, test, FUN=f1, na.action=NULL) Or library(dplyr) test %>% group_by(name) %>% summarise_each(funs(f1)) Or library(data.table) setDT(test)[, lapply(.SD, f1), name] ...

Your best bet is to flatten or "stretch out" the matrix. Try the following library(magrittr) library(data.table) library(reshape2) ## Let IDs be the metadata data.frame DT_ids <- as.data.table(Ids, keep.rownames=TRUE) # DT_ids[, rn := as.numeric(rn)] setkey(DT_ids, rn) ## Let M be the interactions matrix ## Reshape the interactions data into a tall...

excel,if-statement,sum,excel-formula,sumifs

Does your formula for J3 look like this? SUMIFS(B$3:B$22, D$3:D$22, G3, E$3:E$22, H3)...

sum,java-8,grouping,java-stream

First I assume that you are using java.util.Date (though I'd advise you to move to new java.time API). Second, I assume that Something class has also properly implemented equals and hashCode. Also more getters are necessary: String getParentName() { return parentName; } Date getAt() { return at; } Under these...

SELECT 'acc1167' as tbl_name,SUM(amnt) FROM acc1167 union SELECT 'acc1152' as tbl_name,SUM(amnt) FROM acc1152 union SELECT '1167' as tbl_name,SUM(amnt) FROM accnt WHERE ACCNO = '1167' union SELECT '1152' as tbl_name,SUM(amnt) FROM accnt WHERE ACCNO = '1152' ...

I'm terrible with this theoretical run-time analysis stuff, but this might help to look at things another way: for i_{1,n} { if (j>i) -> B[i,j] = sum of A[i],A[i+1],...,A[j] else -> B[i,j] = 0 } Which (I think) happens to be the same as: for i_{1,n} { for j_{1,n} {...

Algorithm: 1. take today date 2. know day of the week as number 3. count monday and sunday dates 4. create empty array 5. walking along the array (foreach array as obj) if array[date] does not exist, add array[date] (obj->[start]) if array[date][id] already exist, add dursum (obj->id obj->dur) else array[date][id]...

scala,sum,binary-tree,immutability,case-class

Using a List is not a good idea for the reason that when you concatenate two lists, you will have to copy their content so an O(n) complexity operation each time you encounter a Node. If you really want to do it with Lists, you could do it like this:...

Try this: select case when country in('Austria','Spain','Italy') then Country else 'Other' end as Country, sum(Jan) as Jan, sum(Feb) as Feb, sum(Mar) as Mar from t group by case when country in('Austria','Spain','Italy') then Country else 'Other' end Fiddle http://sqlfiddle.com/#!4/93719/2...

python,python-3.x,sum,concatenation

This doesn't work. sum() is for adding up numbers, not strings. Use ''.join() instead. >>> ''.join(['good ', 'morning']) 'good morning' ...

sql,sum,subquery,correlated-subquery

AFAIK you can't do a correlation into a Derived Table. But you can rewrite your query to a non-correlated subquery: SELECT People.IDPerson, People.NAME FROM ( -- Selects people that never made a sticker transaction SELECT p.IDPerson, p.NAME FROM Person p LEFT JOIN StickerTransaction sT ON p.IDPerson = sT.IDPerson WHERE sT.IDPerson...

Sub Test() Dim y As Variant Dim firstRow As Variant Dim lastRow As Variant lastRow = Range("C" & Rows.Count).End(xlUp).Row firstRow = Cells(lastRow, 3).End(xlUp).Row If IsNumeric(Cells(lastRow + 1, 1)) And IsEmpty(Cells(lastRow + 1, 2)) Then Cells(lastRow + 1, 3).Formula = "=SUM(C" & firstRow & ":C" & lastRow & ")" End If...

Don't mix JSTL with scriptlets. Actually, don't use scriptlets at all. <c:set var="sum" scope="page" value="0.0"/> <c:forEach var="user" items="${page.content}"> <c:set var="sum" scope="page" value="${sum + user.grade}" /> ${user.grade}<br/> </c:forEach> Better yet, do your summation in a controller....

Try this: n = 26; result = reshape(sum(reshape(x.', n, []), 1), [], size(x, 1)).'; You can look at intermediate results to see how this works: reshape(x.', n, []), then sum(reshape(x.', n, []), 1) etc...

sql,sql-server,select,sum,boolean

OK, Now that we have the DDL (unfortunately without the DML but only one row). we can provide a solution :-) firstly! I highly recommend NOT TO USE the solution above, THERE IS NO NEEDS for loops even you not use fixed data length we know the max len (50)....

I would do the sum in php cycle, not in sql (the ORDER BY clause is also important here): $sql = "SELECT distance FROM axle WHERE train_id = :train_id ORDER BY axle ASC"; ... $total_distance = 0; foreach($number_of_axles as $number_ofaxles){ $total_distance += $number_ofaxles['distance']; echo $total_distance/25000*100; } ...

sql,select,sql-server-2012,sum,multiplying

How About This. The case is just to avoid div by Zero errors. SELECT sum( Isnull(tbl.quantity,0) * Isnull(table2.price,0) ) as grandtotal, tbl.PartNumber, Sum(tbl.quanity), case when Isnull(Sum(tbl.quanity),0) = 0 then null else sum(Isnull(tbl.quantity,0) * Isnull(table2.price,0) ) / Sum(tbl.quanity) end as Price FROM (SELECT left(itemnumber, 10) as itemnumber, quantity FROM table1 WHERE...

Simplest answer, if you're only adding two dataframes: # fill_value parameter specifies how to treat missing rows, since you can't add NaN (i.e. add 0) df3 = df1.add(df2, fill_value=0) df3 Out[18]: 0 a 3 b 7 c 13 d 19 However, if you want to add more than two, the...

sql,count,sum,base,libreoffice

You can do with conditional aggregation like: SELECT SUM(CASE WHEN Q1_by = 'me' THEN 1 ELSE 0 END + CASE WHEN Q2_by = 'me' THEN 1 ELSE 0 END + CASE WHEN Q3_by = 'me' THEN 1 ELSE 0 END + CASE WHEN Q4_by = 'me' THEN 1 ELSE 0...

sql-server,sum,data-warehouse,percentage

you can use Running Total query and use your returned table as sub query as the following: SELECT [DimOrganization].OrganizationName , Value from ( SELECT x, Value, (SELECT SUM(Value) FROM (select top 10 [FactFinance].[OrganizationKey] as x,SUM([Amount]) as [Amount] , SUM([Amount]) / (select sum([Amount]) from [FactFinance])as Value FROM [FactFinance] group by([FactFinance].[OrganizationKey]) order...

You can try this way : for $x in //onboard/*[*] return sum($x/*) That reads : for $x as each child of <onboard> having at least one child element, return sum of all children element of $x. UPDATE : Turned out that the problem was due to empty element(s) passed to...

"So I started doing this for you and saw when I was selecting records that the WHERE - BETWEEN clause isn't working correctly. I set the dates as above and I'm getting records from March in there too. Have I done something wrong with the date format such that it's...

sql-server,join,group-by,sum,sql-update

Try this: update w set hist_amt_due = d.s from @work w join (select pmt_customer_no, sum(isnull(due_amt, 0)) s from @due_cte group by pmt_customer_no)d on w.pmt_customer_no = d.pmt_customer_no ...

Group by is implying distinct values based on subscription.id so probably if you take the group by you you will get something like 8,8,8,8,8,7,7,7,7,7 due to the joins and such. With the group by you only get the distinct values of 8 and 7. When you do sum with the...

scala,tree,sum,pattern-matching,case-statement

This works. @marios answer led me to the right path, even though his implementation had a bug and didn't work. def leafSum(lst: List[Any]): Int = { lst.foldLeft(0) { case (sum, node: List[_]) => sum + leafSum(node) case (sum, leaf: Int) => sum + leaf } } ...

Just add the desired date range to the WHERE clause: SELECT B.TENDER_TYPE_ID, A.TENDER_TYPE_DESC, SUM (B.TENDER_AMT) FROM POS_TENDER_TYPE_HEAD A, SA_TRAN_TENDER B, SA_TRAN_HEAD C WHERE A.TENDER_TYPE_ID = B.TENDER_TYPE_ID AND B.TRAN_SEQ_NO = C.TRAN_SEQ_NO AND C.TRAN_DATETIME BETWEEN TO_DATE('01-JAN-2015', 'DD-MON-YYYY') AND TO_DATE('31-MAR-2015', 'DD-MON-YYYY') GROUP BY B.TENDER_TYPE_ID, A.TENDER_TYPE_DESC I also suggest you get used to using...

You need to use Substring(ID,1,1) ...

javascript,arrays,for-loop,sum

The problem is you loop one too many elements, use < instead of <= var districts = [{bars:0}, {bars:2}, {bars:2}, {bars:3}, {bars:0}, {bars:3}]; //these are all the districts in the game + the amount of bars on them var PlayerControlledDistricts = [districts[0], districts[2], districts[3]]; //these are the 3 districts the...

When using a subquery you need to get a single row back, so you're effectively doing a query for each record in the item table. UPDATE item i SET popularity = (SELECT SUM(io.quantity) FROM itemorder io WHERE io.item_id = i.item_id) > 3; An alternative (which is a postgresql extension) is...

Your group by needs to match exactly what you want to group it by. Since you're altering the text for "dbo.IDENTITY_MAP.Name", you need to use that as the group by. SELECT replace(replace(replace(replace(dbo.IDENTITY_MAP.Name,'My Company\',''),'-VLAN2',''),'.VLAN2\',''),'.Instr\','') as Site, Count (CASE WHEN dbo.SEM_AGENT.AGENT_VERSION LIKE '11.%' AND dbo.SEM_COMPUTER.OPERATION_SYSTEM LIKE 'Windows%' THEN 1 END) AS 'Windows-SEP-11',...

//returns parent row for the button function getRow(el) { var row = el.parentNode.parentNode; return row; } //returns number from val1 function getVal1(row) { return +row.querySelector(".val1").value || 0 } //returns number from val2 function getVal2(row) { return +row.querySelector(".val2").value || 0 } //sets result function setResult(row, val) { row.querySelector(".result").value = val...

you can write like outer query and your query as inner query .. SELECT Sum(Ontime) AS Ontime ,AVG(actual) AS Actual ,Sum(task_count) AS task_count FROM ( SELECT AVG(CASE WHEN a.ontime = 'on time' THEN 100 ELSE 0 END) * COUNT(a.country) AS ontime ,AVG(CASE WHEN a.accuracy = 'accurate' THEN 100 ELSE 0...

Select cell with formula in column F. Grab the black square in the bottom right corner of the selected cell (pressing left mouse button). Move mouse to the last cell in column F. Release left mouse button. It's done.

arrays,algorithm,sum,big-o,subset

You can easily solve this problem with a simple recursive. def F(arr): if len(arr) == 1: return (arr[0], 1) else: r = F(arr[:-1]) return (11 * r[0] + (r[1] + 1) * arr[-1], 2 * r[1] + 1) So, how does it work? It is simple. Let say we want...

You need a DISTINCT to get rid of duplicates. This is using a CASE: COUNT(DISTINCT CASE WHEN children.status > 0 THEN parent_id END) You could probably do the same with IF, but I prefer Standard SQL's CASE: COUNT(DISTINCT IF(children.status > 0, parent_id, NULL))...

Based on OP's clarifications via comments on the question, it could be done using SUM() analytic function to get the running total, and then filter it based on the condition. Table: SQL> SELECT * FROM t; ID_PRIM ID DT MOVED ---------- ----- --------- ---------- 1003 12_1 01-NOV-13 2 1003 12_2...

Let's suppose the following two arrays and their "sum": Array 1: 1 2 3 4 5 6 7 8 9 [length = 9] Array 2: 2 4 6 8 2 4 6 [length = 7] Sum : 3 6 9 12 7 10 13 8 9 [length = 9] Pay...

ruby-on-rails,ruby,database,attributes,sum

It would be better to make before_update, that would take care of updating your value in DB, whenever you change tips_value or profile_value, and from anywhere. In your Affinity model add before_update :sync_value_amount private def sync_value_amount self.value = self.tips_value + self.profile_value end ...

php,html,checkbox,sum,comparison

I made the necessary changes to your code to make it work and do what I think you want. Here it is, the changes are pointed by commented arrows "// <=======", try it and let me know : <html> <head> <link rel="stylesheet" type="text/css" href="style1.css" /> </head> <?php if(isset($_POST['formSubmit'])) { $breakfastMC...

The sums of the first ten and 100 fibonacchi number would be 88 and 573147844013817084100, respectively: >>> cache = {} >>> def fib(n): if n == 0: return 0 if n == 1: return 1 if not n in cache: cache[n] = fib(n - 1) + fib(n - 2) return...

date,reporting-services,sum,range

The first: you shouldn't use expression like Fields!Calculated_DueDate.Value.value - it is error. The second: There are error in arrangement of brackets. The third: In SSRS 2008R2 call of function today() marks as error. You can use expression DateTime.Today to get current date without errors markup. So, your expression should look...

If you're using numpy, then you also use ~ to invert things just like MATLAB. See: What does the unary operator ~ do in numpy?. The sign function also exists in numpy. You use numpy.sign. Therefore, the code above is simply: >>> import numpy as np >>> indi = ~np.abs(indexd)...

Maybe this can get you started. I think it is what you expressed. However I think it would make more sense with an initialization like rnorm(nx,1,0.01) - i.e. something smoother. It might also make sense to have xsub interpolate between the generated points (i.e. make it a continuous function). It...

You can use Intersect and EntireRow to figure out which cells to sum. I would give them to Application.Sum to do the math. Another call to Intersect will let you know if the changed cell is in the "boxed" area. Private Sub Worksheet_Change(ByVal Target As Range) Dim sum As Double...

matlab,sum,continuous-integration,convolution,fourier-descriptors

That is because conv is only defined for numeric inputs. If you want to find the convolution symbolically, you'll have to input the equation yourself symbolically using integration. If you recall, the convolution integral is defined as: Source: Wikipedia Therefore, you would do this: syms x tau; F = int(h(tau)*x1(x-tau),'tau',-inf,+inf);...

You can sum the items in each table to get the total sum and then cross join both aggregate results to table2: UPDATE table2 SET InvAmnt = sum1 * InvAmnt / sum2 FROM table2 CROSS JOIN (SELECT SUM(cost) AS sum1 FROM table1) t CROSS JOIN (SELECT SUM(InvAmnt) AS sum2 FROM...

sum(tot_hours) OVER (PARTITION BY empid ORDER BY empid ) AS tot_hours Your ORDER BY is incorrect. If you want the running SUM on the TOT_HOURS, then you should order by tot_hours. For example, the below query will calculate the running sum of salary of employees in each department: SQL>...

There exists a rather simple O(n) approach using the so-called "two pointers" or "two iterators" approach. The key idea is to have two iterators (not necessarily C++ iterators, indices would do too) running on the same array so that if first iterator points to value x, then the second iterator...

You can make an arbitrary decision to represent the the "smaller" IP address first and the "larger" second. From there on, it's a simple group by and sum: WITH t1 AS ( SELECT LEAST(source_ip, distination_ip) AS ip1, GREATEST(source_ip, distination_ip) AS ip2 , usage FROM receiver ) SELECT ip1, ip2, SUM(usage)...

I tried this solution on sql fieddle: link Table creation: CREATE TABLE time_table ( id integer, time time ); Insert data: INSERT INTO time_table (id,time) VALUES (1,'1:23:23'), (2,'4:00:23'), (3,'9:23:23') query the data: SELECT sum(s.time) FROM time_table s; ...

sql-server,insert,sum,sql-update

Well, you can do it pretty straightforward and absolutely according to your task description: declare @sumA decimal(29, 10), @sumB decimal(29, 10), @ratio decimal(29, 10) select @sumA = sum(amnt1) from accnt where ven1 = 1167 select @sumB = sum(amnt) from acc1167 select @ratio = @sumB / @sumA update acc1167 set amnt...

The INDEX function can provide a valid cell reference to stop using a MATCH function to find an exact match on 0. This formula is a bit of a guess as there was no sample data to reference but I believe I have understood your parameters. =SUMIFS(C2:index(C2:C35, match(0, A2:A35, 0)),...

fix adjust dynamic sql string to add group by on fields ItemNumber,Item_Name as below static query SELECT ItemNumber,Item_Name,sum(Qunatity) as Qunatity1 ,sum(FOCQty) as FOCQty1 from toCSV5 where ItemNumber = 'I1' group by ItemNumber,Item_Name ; output +------------+-----------+-----------+---------+ | ItemNumber | Item_Name | Qunatity1 | FOCQty1 | +------------+-----------+-----------+---------+ | I1 | ABC |...

sql,oracle,sum,group,partition

Assuming that for a maingroup/subgroup, the subgroupbudget is always the same (or you only take the highest value for the subgroup), this should work: with t as (select 'Group1' as maingroup,'Name 1' as subgroup, 'random' as random, 500 as subgroupbudget from dual union all select 'Group1','Name 1','random2',500 from dual union...

Here I am doing it using C: An array A[] of n numbers and another number x, determines whether or not there exist two elements in S whose sum is exactly x. METHOD 1 (Use Sorting) Algorithm: hasArrayTwoCandidates (A[], ar_size, sum) 1) Sort the array in non-decreasing order. 2) Initialize...

Without knowing how to treat those records with OFFMOVEMENTDATE and LOCATION null I can give you this query(it should be a little more efficient because it doesn't perform an expensive self join) that simply ignore those rows: with act_data (animalid, movementdate, offmovementdate, move, location, death, rsk) as ( values(1, date'1998-01-01',...

sql-server,sql-server-2005,sum,overlapping

This is one way to do it: ;WITH CTErn AS ( SELECT activity_client_id, activity_type, activity_start_date, activity_end_date, ROW_NUMBER() OVER (PARTITION BY activity_client_id ORDER BY activity_start_date) AS rn FROM activities ), CTEdiff AS ( SELECT c1.activity_client_id, c1.activity_type, x.activity_start_date, c1.activity_end_date, DATEDIFF(mi, x.activity_start_date, c1.activity_end_date) AS diff, ROW_NUMBER() OVER (PARTITION BY c1.activity_client_id ORDER BY x.activity_start_date)...

neg <- sum(vec[vec<0]); pos <- sum(vec[vec>0]); ...

sql,sql-server,sql-server-2008-r2,sum

You need a table of all the statuses. If you don't already have a table, you can do this in the query itself: SELECT ClientDeliveryStatus, COUNT(t.ClientDeliveryStatus) AS Total FROM (SELECT 'Past Due' as cds UNION ALL SELECT 'Due Tomorrow' UNION ALL SELECT 'Due Today' UNION ALL SELECT 'Due Beyond' )...

You can use SUM in a co-related subquery or CROSS APPLY like this Co-related Subquery SELECT ID,(SELECT SUM(Qty) FROM B WHERE B.id <= C.id) FROM B as C ORDER BY ID Using CROSS APPLY SELECT ID,D.Qty FROM B as C CROSS APPLY ( SELECT SUM(Qty) Qty FROM B WHERE B.id...

Sub summAndMerge() lastrow = Worksheets("A").Range("A65536").End(xlUp).Row For i = 2 To lastrow If Cells(i, 1).Value <> Cells(i - 1, 1).Value And Cells(i, 1).Value <> Cells(i + 1, 1).Value Then intUpper = i Debug.Print ("<> -1 and <> +1 " & intUpper) End If If Cells(i, 1).Value <> Cells(i - 1, 1).Value...