FAQ Database Discussion Community

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...

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...

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....

Select organizations that their income represent around 60% of the total income SQL Server2008

sql-server,sum,data-warehouse,percentage
In advance, I apologize for imperfect English In this data warehouse, we have organization which composed of multiple Organizations, I have [FactFinance] table which has information about the income of each organization. I have the following query in data warehouse which select the (Organization Name) from the [organization dimension table]...

Percentage for each result where id = id

php,html,for-loop,mysqli,sum

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...

Select left 10 numbers, left join for a price from second table, and then sum, SQL

sql,select,sql-server-2012,sum,multiplying
I am currently working in sql 2012 visual management studio. I have two tables. Table1 has three columns (ItemNumber as varchar, Quantity as int, and TimeOrdered as datetime). Table2 has 2 columns (ItemNumber as varchar, and Price as float). Please note these item numbers are not the same, the part...

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,...

Python 3.x - Using the sum function to concatenate strings in a list

python,python-3.x,sum,concatenation
x= ['Good', 'morning'] # example list of strings I have to use sum(x) to concatenate both and print (x). I'm having trouble converting the list into integers for the sum function to work. >>>print (sum(x)) 'Goodmorning' ...

VBA SUM Variable Range

excel,vba,variables,sum,range
I want a code to sum the Variable rows up if certain condition is met. e.g. If A12 is numeric and B12 is empty then insert a fomula in cell C12 to sum C3:C11. Then perform the same action at C22 and C30. The problem I have is don't...

Translating mathematical functions from MATLAB to Python

python,matlab,numpy,sum,sign
I am currently working on a project which involves translating a program which runs in MATLAB to Python to increase speed and efficiency. However, I have hit a stumbling block. First, I am confused as to what the tilde(~) indicates in MATLAB, and how to represent that in a corresponding...

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 | |...

Double sum and how to make a matrix to plot in 3D? (surface plot)

matlab,function,matrix,plot,sum
Edit To clarify. I have a function f that takes in 5 arguments z,x,y,n,m. The order of events should go as follows: Upon calling the function test, the variable z is assigned, say z = 1. A linear combination is created by adding f with with each of elements of...

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?...

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...

PHP compare 2 arrays (Mac donalds and Burger King)

php,html,checkbox,sum,comparison
I have to make a comparison site for the products from MC and BK. http://informaticaphp.net84.net/home.php <- that's how far I am. This is the code : <html> <head> <link rel="stylesheet" type="text/css" href="style1.css" /> </head> <?php if(isset($_POST['formSubmit'])) {$breakfastMC = $_POST['BreakfastMC'];$breakfastBK = $_POST['BreakfastBK']; if(empty($breakfastMC)) { echo("<p>You didn't select any food.</p>\n");...

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?...

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...

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...

SUBTRACT two columns SUM, then SUBTRACT the result and SUM the result to show minimum quantity

mysql,count,group-by,sum
Is a little complicated the question, but I need to show the SUM of SUBTRACT two columns SUM and then SUBTRACT the result. Right now I have the result (1,1,1) when the minimum quantity is below of "x" value for each product but right now show me three buttons(because there...

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...

how to sum a field and distribute the sum between records in another table

sql,sql-server,select,sum

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...

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...

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...

SUM query, between dates, grouped by employee

sql-server-2012,sum,aggregate
I have 4 tables, structured as follows: More verbosely, a project consists of a set of tasks. An employee can add one or more notes to the database, specifying the amount of time spent on that task on any given date. I would like to find for each employee, how...

SUM() Function not Working in sqLite

sqlite,select,group-by,sum
We have a table that is like this: Customer ItemNumber Item_Name Qty FOCQty C1 I1 ABC 10 2 C2 I2 BCA 12 2 C3 I1 ABC 3 3 C4 I2 BCA 05 1 But we want it to be like this: ItemNumber Item_Name Qty FOCQty I1 ABC 13 5 I2...

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...

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...

R - Compute increments for use in power variation

r,sum,binomial-coefficients
I am working with high frequent data, which is a vector X of decimal numbers. I would like to code the following in R, but am at complete loss where to start: n decides how big intervals we are looking at. This can be any value, this is not so...

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...

How to perform boolean addition on an SQL select

sql,sql-server,select,sum,boolean
I have the following data in a database table column named timeSchedule 00100110 00010100 00110000 00110011 Boolean addition would result in 00110111 Is there a way to do this in sql? Something like select sumboolean(timeSchedule) from myTable Someone asked for DDL+DML.. here is an example: CREATE TABLE [dbo].[myTable]( [musPracticeID] [int]...

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...

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 * ....

Extracting raster values, from maximum, to cumulatively sum to x

r,sum,raster
I am trying to determine the location of raster cells that add up to a given amount, starting with the maximum value and progressing down. Eg, my raster of 150,000 cells has a total sum value of 52,000,000; raster1 <- raster("myvalues.asc") cellStats(raster1,sum) = 52,000,000 I can extract the cells above...

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...

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...

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...

sum up cells in matrix according to different hierarchical level

r,matrix,sum
I am using R to make a heatmap from binary interactions. The matrix looks as following 9 401 562 68 71 569 700 9 0 1 0 0 0 0 1 401 0 0 1 0 0 na 1 562 0 1 0 1 1 0 1 68 1 1...

SUM previous value in php

php,mysql,sum
How can I create a function which can SUM the previous row with the new row and show me the result per each row?. Like this in the column"SALDO": | Cantidad | nombre del producto | Precio compra | Precio DNM | Precio venta | Total compra | Saldo |...

Efficient way to sum overlapping date intervals with several groupings in PostgreSQL version 8.4

sql,postgresql,group-by,sum
Hello I'm new to stackoverflow and relatively new to psql so please be lenient if I do things wrong. I have a large data set showing animal movements that looks a little like this: animalid | movementdate | offmovementdate | location | rsk ========================================================== 1 | 1998-01-01 | 1998-04-01 |...