FAQ Database Discussion Community

jsp,sum,el

## Count the maximum amount of numbers in an array which sum is an even number

java,arrays,performance,sum
I have this code, which, as I understand it, searches for the maximum amount of consecutive numbers in the given array, which sum is an even number. private static int f (int[]a, int low, int high) { int res = 0; for (int i=low; i<=high; i++) res += a[i]; return...

## Subtract two columns of different tables

sql,postgresql,sum,aggregate-functions,subtract
I have two unrelated tables: contribution(id,amount, create_at, user_id) solicitude(id, amount, create_at, status_id, type_id, user_id) I need to subtract the sum of the amount of the contribution and of the solicitude from a user, but that result can't to be negative. How can I do this? Function or query? I tried...

## Return 0 in sum when no values to sum - sql server

sql,sql-server,sql-server-2008-r2,sum
Trying to return 0 if any of these columns have no values returned, in my particular case 'Past Due' has no values to total, so it is returned, so i get the return in the second snippet here. How can I return something if these are no values to count?...

## SQL query sum unique records

sql,sql-server,tsql,sum
I'm trying to sum all sales of one period of time of a selling vehicle. The problem is that every product sold is one row whit amount and price and a total of the bill and the bill number. So I have 2 options: multiply ever sold product amount whit...

## countif and sumif each row without grouping

sql,sum,countif
I'm trying to figure out sumif and countif using SQL Here is the code to start with: SELECT ItemID , Supplier , Cost , SalePrice FROM countifExpirement I'm trying to create this output: First four columns already have data and i'm trying to create calculated fields to calculate duplicate Items,...

## sum of Digits through Recursion

java,function,recursion,sum,digit
I am trying to make a recursive function which should return the sum of digits. The function should have only one argument. So far I have this; public int sumDigits(int n) { if(n%10 == n) // last digit remains return n; else{ int rightdigit; rightdigit = n%10; // taking out...

## Working with sums in R

r,sum
How does one work with sums in R? I can't seem to find an easy way to calculate sums \sum_{i=m}^n a_i. There are three things to decide here; where summation starts, where it ends, and what elements are to be summed. I have a data frame df and I would...

## VBA to sum payments for a specific person and set the sum in a merged cell

excel,vba,excel-vba,sum
I have a very large excel file, around 50K rows. In Column (C) I have the person numbers 1,1,1,1,2,3,4,5,.... and in Column(N) I have the payments for this person, so the data looks like What I want is to sum All payments for one person from column N and set...

## How to SUM() each row into another column

sql,oracle,sum,running-total
I have this table | ID_prim | ID (FKey) | Date | Moved Items | |:-----------|:------------|-------------:|:------------:| | 1003 | 12_1 | nov 2013 | 2 | | 1003 | 12_2 | okt 2013 | 3 | | 1003 | 12_3 | dec 2014 | 5 | | 1003 | 12_4...

## SQL SUM count of parents with active children

mysql,sql,select,sum
'SUM(IF(children.status > 0, 1, 0)) AS count' I currently have the above code in my select statement, however it returns the count of children who are active, rather than the amount of parents who have active children. While a large portion of my data is single children elements, there are...

## Dsum Function in Query Field not working properly

ms-access,sum,ms-access-2010,ms-access-2013,running-total
I have a date based query that returns two fields, Week Ending Date and L2N, and I want to add a third field that provides a rolling total of the L2N field. I have tried using DSum as follows: RunL2N: DSum("[L2N]","Occupied Apts & L2N", "Week Ending Date=" & "'" &...

## Finding a number that is sum of two other numbers in a sorted array

arrays,sorting,find,sum
As it said in the topic, I have to check if there is a number that is the sum of two other numbers in a sorted array. In first part of the question (for a unsorted array) I wrote a solution, just doing 3 loops and checking all the combinations....

## Calculate the sum of time column in PostgreSql

postgresql,sum
Can anyone suggest me, the easiest way to find summation of time field in POSTGRESQL. i just find a solution for MYSQL but i need the POSTGRESQL version. MYSQL: calculate sum time with mysql SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(timespent))) FROM myTable; Demo Data id time 1 1:23:23 2 4:00:23 3 9:23:23 Desired Output...

## Oracle Get sum of distinct group without subquery

sql,oracle,sum,group,partition
I already have a working example which does exactly what I need. Now the problem is, that I'm not really a fan of subqueries and I think there could be a better solution to this problem. So here is my (already) working example: with t as ( select 'Group1' as...

## SQL select sum/average all rows

sql-server,select,sum,average
i have this code who picks rows from table, but i need to make him return sum of all rows or avg of all rows. I tried to put code in other select like select within sum(select), but its not working, any ideas how to make it work? Sorry for...

## Summing results of an function for each element of an array

c++,arrays,function,for-loop,sum
I want to make an array, where each element sums up the result of a function"CosineEffect". For example if the "heliostatNumber" is 3, each element in the "Cosine" array should be the sum of three results of funcion"CosineEffect". But when I print the result out, it seems that they dont...

## Sum on case expression when working with dates

sql,sql-server,date,sum,case
I'm looking to create a view which will output the data in the following format AgedPeriod BillValue Status <1 35000 Outstanding 1-3 23386 Outstanding 3-6 5000 Outstanding I can use the code below to SUM each case statement into a new column and I could name the column headings after...

## PHP, MySQL How to Sum data in two different date range

php,mysql,sum
Im having trouble doing this +------------+------------+------------+ | Name | year | Value | +------------+------------+------------+ | Name1 | 2015 | 100 | | Name1 | 2014 | 150 | | Name1 | 2013 | 200 | | Name2 | 2015 | 130 | | Name2 | 2014 | 160 | |...

## sum up each individual values from 2 arrays

javascript,arrays,sum,element
I'm trying to understand this conditional loop to sum up each elements from 2 arrays, but I stumble upon the latter part, I don't understand what is to be achieved there. Can you please explain to me? function Arrays_sum(array1, array2) { var result = []; var ctr = 0; var...

## Correlated subqueries: How to get the ID of the outer query?

sql,sum,subquery,correlated-subquery
I have multiple tables : Person --------------------- IDPerson, Name StickerTransaction --------------------- IDTransac, IDPerson, IDSomeoneElse, NbStickersReceived, NbStickersGiven Purchase --------------------- IDPurchase, IDPerson, NbStickersBought I'm trying to get every person who never made a transaction or currently have 0 sticker. To have the number of stickers a person has, here's the formula :...

## Multiple Datasets in one Table OR show distinct Value in Table (SSRS)

mysql,reporting-services,sum,aggregate-functions
I am fairly new to SSRS and currently I'm working on building my first reports. I encounter some problems with a report that in my opinion should be fairly easy, but so far I couln't find the right answer. I have a table similar to this: Material - - Date...

## I am trying to sum probes count within subgroup of a dataframe in R [duplicate]

r,sum
This question already has an answer here: Idiomatic R code for partitioning a vector by an index and performing an operation on that partition 3 answers I am biochemistry student working with breast cancer data (Copy number probes). I have the following data frames for primary breast tumors: Patient...

## SUM of two timestampdiff in minutes

mysql,timestamp,sum
I've searched through many sites but was unable to find a solution to this. I'm trying to sum two timestampdiffs when they are in minutes: SELECT COUNT(*) from table WHERE TIMESTAMPADD(MINUTE, TIMESTAMPDIFF(MINUTE,first_date,second_date), TIMESTAMPDIFF(MINUTE,fourth_date,third_date) ) > 15 AND status='Pending' This is where I am currently at but it returns an empty...

## How to find top usage of IP pairs in PostgreSQL?

sql,postgresql,select,sum
I have a table with source_ip and destination_ip and usage. now i want to find top usage for ip pairs. My table is like this: source_ip | destination_ip | usage 192.168.1.1 | 192.168.1.2 | 20 192.168.1.2 | 192.168.1.1 | 30 192.168.1.3 | 192.168.1.2 | 20 192.168.1.2 | 192.168.1.3 | 20...

## Oracle SQL Groupby and SUM

sql,oracle,group-by,sum
I have the following query that groups every tender id and description and shows its total Sum: 3020 American Express 20 1000 Cash - primary currency 9903.25 3120 House Card 2605.56 4070 Purchase Order 668.25 3000 Visa 26005.19 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...

## comprare aggregate sum function to number in postgres

postgresql,sum,case
I have the next query which does not work: UPDATE item SET popularity= (CASE WHEN (select SUM(io.quantity) from item i NATURAL JOIN itemorder io GROUP BY io.item_id) > 3 THEN TRUE ELSE FALSE END); Here I want to compare each line of inner SELECT SUM value with 3 and update...

## Group array by common id and date and sumarize to calendar table

php,arrays,date,sum,group
I've been ripping my hair out on this one. Struggling with understanding how i really want to attack this problem, even the basic logic... I've got this dataset: Array ( [0] => stdClass Object ( [id] => 233773869 [pid] => 9919304 [tid] => 6754304 [uid] => 1502708 [project] => **HIDDENNAME**...

## SQL: Query to merge (some) rows

sql,oracle,merge,sum,group
I'm currently trying to create a query that merges some rows to one category and sums up the related values. I'll try to illustrate it with the following example: Country | Sales Jan | Sales Feb | Sales Mar --------+-----------+-----------+---------- Austria | 100 | 110 | 120 Spain | 120...

## SUM case returns value without GROUP BY

mysql,sql,group-by,sum,case
When I add SUM around my case select, it returns the summed value without the GROUP BY. The query I am using, without the SUM, is the following SELECT CASE WHEN subscription_types.type = 'Succes lidmaatschap' THEN 7 ELSE 8 END FROM subscription_used INNER JOIN training_sessions ON training_sessions.id = subscription_used.training_session_id INNER...

## Scala sum of all elements in the leaves of a tree using pattern matching

scala,tree,sum,pattern-matching,case-statement
I'm learning Scala by working the exercises from the book "Scala for the Impatient". One of the questions asks: /** * Q5: One can use lists to model trees that store values only in the leaves. For example, the list ((3, 8) 2 (5)) * describes the tree * ....

## EXCEL SUM cells which contain string

string,excel,search,sum,formula
I'm trying to write a formula in Excel which will run down a column, and search each cell in that column for the presence of a string (which may be in the middle of another string); if the string is found, move over to another column and add the value...

## SQL COUNT And SUM

sql,count,sum,base,libreoffice
I have a table having this table done_by Var_ID| Var_name| Q1_by |Q2_by|Q3_by|Q4_by 1 | abc | me | me |me |you 2 | cba | me | me |you |you 3 | abd | me | you |you |me the result i want to get is get all the total...

## R - passing variable containing column name into sum()

r,variables,sum
Let's say we've attached a data from a table containing column 'banana' with numeric values. colName='banana' Now we want the sum of that column, assuming we don't know what it's called, we only know the name is being stored in the colName variable. How do we call the sum() function?...

## excel count/sum stop count/sum match?

excel,count,sum,match,sumifs
I have tried to see if this question has been asked before, but I can't seem to find an answer. I have a column of cells (>3000 rows), with either a value of 1 or 0 (call this column A). The value will depend on the value in column B...

## How to calculate a sum of products of array elements using a fast Fourier transform?

c++,arrays,c++11,sum,fft
I have some binary array. For example, let my array is: int a[] = {1, 0, 0, 0, 1, 0, 1, 0, 1} I want to calculate the values based on this formula: How to calculate this function, using a fast Fourier transform? I have a large array and I...

## Exact sum of a long array

java,sum,integer-arithmetic
In order to get the exact sum of a long[] I'm using the following snippet. public static BigInteger sum(long[] a) { long low = 0; long high = 0; for (final long x : a) { low += (x & 0xFFFF_FFFFL); high += (x >> 32); } return BigInteger.valueOf(high).shiftLeft(32).add(BigInteger.valueOf(low)); }...

## C# remove item from list of integers int[] l = {1,2,3} - or use recursion to add them

c#,list,recursion,integer,sum
So there's this blog that gives Five programming problems every Software Engineer should be able to solve in less than 1 hour and I'm just revisiting some of the concepts. The first question reads Write three functions that compute the sum of the numbers in a given list using a...

## Percentage for each result where id = id

php,html,for-loop,mysqli,sum

## mysql: order -> limit -> sum… possible?

mysql,group-by,sum,sql-order-by,sql-limit
i am loosing it over the following problem: i have a table with participants and points. each participant can have up to 11 point entries of which i only want the sum of the top 6. in this example lets say we want the top 2 of 3 +----+---------------+--------+ |...

## Query to sum the previous values

sql,oracle,sum,running-total
I have the table strucure as in the image. I need to get the values added to the sum of previous values(shown in the REQUIRED RESULT) I tried with the following query SELECT empid, sum(tot_hours) OVER (PARTITION BY empid ORDER BY empid ) AS tot_hours FROM empDet ORDER BY empid...

## Ruby Update Attribute with Sum of Database Attributes

ruby-on-rails,ruby,database,attributes,sum
I have a database with three values: tips_value, profile_value, and value. For each entry in the database, whenever I update the entry, I want to take the sum of tips_value and profile_value and assign it to value, but it's not working. Here is my update function: def update @affinity =...

## MySQL select sum then two grouping

php,mysql,sum,where,having
I have a table with id, name, type, price, name, date and type_2. Theses are bills. What do I have to query if I want to select all the bills between First May and 6 june that are to Mr. X. My code so far: SELECT sum(prix) from notes_de_frais WHERE...

## How do I find the sum of every X rows in a matrix in Matlab

matlab,sum,submatrix
I have a 189 x 4914 matrix and am trying to find the sum of each row in each 1x26 sub-matrix. How would I go about this? Many thanks. ...

## Formula to return the sum of a range in Excel for certain rows in a different column

excel,sum,formula
I would like to use formula to return the sum of cells from column B where cell value in column A is equal to a certain week number. The table is below. For example if Week = 1 the SUM would be 40; Week = 2 SUM = 110 ,etc....

## Using Sum in If in Mysql

mysql,sql,select,sum
I want to select the sum of values of my_field. The unit of my_field is in seconds. I don't want to exceed 24 hours, so when it exceeds 24 hours, I want it to select 24*60*60 = 86400. SELECT IF(SUM(my_field) > 86400, 86400, SUM(my_field)) ... This solution doesn't seem to...

## SQL Update with a Group by Statement not working

sql-server,join,group-by,sum,sql-update
I've got two temp tables. In table @due_cte I have a series of Customer_Ids, followed by payment amounts collected throughout various points in the code. The @work table has a lot of various columns including hist_amt_due. What I want to do is update @work with the amt_due from @due_cte but...

## Subquery aggregate function with SUM(CASE SUBQUERY)

sql,sql-server,sum,subquery,aggregate-functions
I'm getting an error while executing my query Cannot perform an aggregate function on an expression containing an aggregate or a subquery. Code: SELECT S.id, SUM(CASE WHEN sc.coverage IN (SELECT number FROM ArrayOfIntegersFromString(@dynamicData)) THEN 1 ELSE 0 END) as sm FROM Storefronts s LEFT JOIN StorefrontCoverages sc ON s.id =...

## How come the data types aren't passing through in this Haskell code?

Trying to learn Haskell. solve :: Int -> Int -> [Int] -> [Int] -> [Double] solve l r a b = [sumfunc(l,r,a,b)] sumfunc :: Int -> Int -> [Int] -> [Int] -> Double sumfunc l r a b = sum([0.001 | x <- [1000*l..1000*r]]) Trying to return the contents of...

## SUM Column SQL SERVER

sql-server-2008,sum
I have a table in SQL Server, and I need to sum a column, like the example below: CREATE TABLE B ( ID int, Qty int, ) INSERT INTO B VALUES (1,2) INSERT INTO B VALUES (2,7) INSERT INTO B VALUES (3,2) INSERT INTO B VALUES (4,11) SELECT *, ''...

## Excel formula: Sum if two columns match

excel,if-statement,sum,excel-formula,sumifs
I need a formula that only sums numbers in a cell from a column if it matches two cells in the same row. In J3 i'm trying to get the sum of all the returns that are from January 2009. In J4 'm trying to get the sum of all...

## Sum, Avg, Max, Min, Count of NULL values

mysql,sql,null,sum
In MySQL SELECT 2+NULL FROM tbl_name will return NULL, since MySQL cannot interpret NULL as a number. But why will SELECT SUM(quantity) FROM tbl_name not return NULL if just one of the values is NULL? The same goes for MIN, MAX, AVG, etc. Since MySQL doesn't know what NULL could...

## how to sum a field from 3 tables and display the results together

sql-server,select,sum
im using sql server and I have 3 tables called accnt, acc1152, and acc1167. each of these tables has an amnt field and a accno field with a few thousand records in it. i need to sum the entire amnt column for acc1152 and acc1167, then sum the amnt column...

## MySQL summing verses and n biggest results

mysql,select,sum
I've a table looking more or less like that: **Day** | **Mileage** ---------------- 1 | 13 2 | 2 3 | 25 4 | 15 5 | 20 6 | 8 7 | 17 8 | 12 9 | 16 10 | 5 How to write a SQL query: Returning...

## Expression can not contain lambda expression Linq

c#,linq,lambda,sum
I have data-table and i am trying to find sum of columns where Id starts with Particular value.I have tried some ways but getting error. result=Convert.ToInt32(dtNew.Compute("Sum(ResPending)", "Substring(ID,0,1)='G'"));//error shows-- Substring() argument is out of range And tried this way also dtNew.AsEnumerable().Where(x => x.Field<string>("ID").ToString().StartsWith("G"));//Expression can not contain lambda expression ...