2022 Developer Survey is open! Take survey.

Questions tagged [sum]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
14 views

MS Access - How to sum matching criteria from two columns in same table

I have an MS Access database with four columns of interest that I am trying to work with. The rows represent sold jobs, and the columns of interest are "OriginalSaleAmount", "...
user avatar
3 votes
2 answers
73 views

How to Join two tables with SUM and GROUP BY in SQL server

I have 2 tables product id designation 1 coca 2 Pepsi 3 Fanta 4 Seven 5 Eight 2)sub_product product_id name quantity 1 sm 10 1 lg 10 1 xl 20 2 1L 10 2 2L 20 2 5L 20 3 Ty 10 3 Sa 20 4 ...
user avatar
0 votes
2 answers
45 views

MySQL: SUM() returns multiple rows when used with joins and grouping

I expected by query, which uses SUM() to return one row, containing the total of all rows. However, it returns multiple rows. SUM() is not adding the rows. SELECT SUM(DISTINCT `sales`.`sale_shipping`) ...
user avatar
0 votes
1 answer
28 views

MySQL: SUM() does not provide sum when used with JOIN

This produces one column with 10 numerical rows: SELECT (`sales`.`sale_shipping`) as `total_shipping` FROM `sales` LEFT JOIN `contacts` ON `sales`.`contact_id` = `contacts`.`contact_id` LEFT JOIN `...
user avatar
1 vote
1 answer
23 views

Calculate running total of "consecutive days without views" for each group, that can restart multiple times within each group based on conditions

Summary I'm working to create a counter metric that shows, for each client's slug, the number of consecutive days of zero views. The counter should increase for each consecutive day that the client's ...
user avatar
  • 21
2 votes
2 answers
463 views

Why does a MySQL 'where' clause with Index make a SUM query slower?

I have a table with 5 million records. Table columns are id, customer_id, status, and amount. When I run a query without Index(status), SELECT SUM(amount) FROM `game_results` WHERE `status` = 1 the ...
user avatar
0 votes
0 answers
20 views

SUM `amount` column based on `transactionType` in Postgresql "transactions" table

I have this table: +--------+-----------------+ | amount | transactionType | +--------+-----------------+ | 10 | Debit | | 5 | Credit | | 15 | Credit | | 20 ...
user avatar
-1 votes
1 answer
28 views

sum with combination

I reproduce the table in image and result I expect. So, I like sum for each item withe the sum of related item. But some item are null or join to himself. I think some array can help me but I don't ...
user avatar
  • 101
0 votes
3 answers
96 views

Query for getting the sum of all unique entries in a column

Currently I'm using mysql to return the sum of all data in a column that have the same word. To do this I'm using the following query: SELECT SUM(CASE WHEN status_to = 'Draft' THEN 1 END) AS draft, ...
user avatar
  • 103
2 votes
2 answers
92 views

Group by x, get other fields too (comparing options and performance)

I'm a novice, trying to find the best way to sum up my data. Oracle 19c. I have a WORKORDER table that has work orders (ISTASK=0) and tasks (ISTASK=1). Tasks are children of work orders. Work orders ...
user avatar
  • 1,377
0 votes
1 answer
37 views

Best practise to compute total amount from individual entries

I want to keep track of the individual and total amount of drinks someone ordered. Table 1 Customer information: customer_id name age 1 Steve 44 4 John 24 Table 2 Drink ...
user avatar
2 votes
2 answers
46 views

T-SQL Group and sum record set based on value in a given column

Take the following example: SELECT * FROM [Orders] WHERE DATE = '2021-09-10' Date FirstName LastName Adults Item Description ---------- --------- --------- ------ --------- -----...
user avatar
2 votes
2 answers
236 views

Zero/NULL Case Trick

In the book Getting Started with SQL, Thomas Nield talks about a technique he calls the zero/null case trick: There is a simple but powerful tool that can apply different filtering conditions to ...
user avatar
  • 1,377
0 votes
1 answer
62 views

Select Result Different On Float Data Type MariaDB 10.4.8 [duplicate]

On MariaDB I have simple table with float data type CREATE TABLE `testatt` ( `id` int(11) NOT NULL AUTO_INCREMENT, `nilai` float DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=...
user avatar
  • 47
0 votes
1 answer
639 views

SQL sql subquery with SUM for totals

I am a little stuck on getting a total value for a column using the SUM function. I want to get the total amount of items for an order from my database. The DB order is made up of several line items ...
user avatar
  • 1
1 vote
1 answer
63 views

SQLite: How do I use the sum function

The code I am using is here CREATE TABLE timecard ( id INTEGER PRIMARY KEY autoincrement, clock_in REAL not null, clock_out REAL, lunch integer, pay_type TEXT ); INSERT INTO timecard ...
user avatar
1 vote
1 answer
2k views

Sum multiple columns, based on distinct values in different Columns

I have the following table, in an Azure SQL DB that has duplicate values that I'm trying to Sum. Here is the Logic: If the PaymentID is unique, then Sum Payment, If the creditID is unique, then sum ...
user avatar
-1 votes
2 answers
315 views

Get rows above and below a certain row, based on two criteria SQL (with SUM)

Say I have a table like so: +----------+---------+------+---------------------+ |student_no|level_id |points| timestamp | +----------+---------+------+---------------------+ | 4 | ...
user avatar
  • 15
1 vote
2 answers
87 views

EAV - COUNT()/SUM() on column and row values with and without NULLs

Assume that there is a table demo with these data: id userID title data 1 1 DATE 01-01-2021 2 1 COLOR RED 3 2 DATE 4 2 COLOR YELLOW 5 3 DATE 04-01-2021 6 3 COLOR RED 5 4 DATE 04-01-2021 6 4 ...
user avatar
1 vote
2 answers
81 views

Cannot SUM the value of a sales amount field - various criteria, sales rep id and date ranges

I wonder if someone can help me with this SQL Server 2016 query. I have two tables with following data TblSalesRep This is the table holding the history of the territories that the sales rep looked ...
user avatar
  • 25
0 votes
1 answer
51 views

Group by Multiple columns and then count different value of same column

I want to achieve the result which tells me the number of males and females of each disability types in each district. each district can have multiple disabilities. So far i have reached the ...
user avatar
4 votes
1 answer
219 views

Performance issues summing a count column over a date range

In our application, we have a query which on an API endpoint to return users who have 'activity' to a named client or matter (one or many - specified by ID). This query takes about 15 seconds to ...
user avatar
0 votes
1 answer
35 views

do a sum for each entry in table [closed]

i got two tables a and b table a contains some names like cobol, php, cmd and a refnumber name ref Cobol 1 php 2 cmd 3 ... table b contains multiple projekts where i used one of table a like ...
user avatar
0 votes
1 answer
17 views

Select parent rows and include cost of children

I have a WORKORDER table that has parent and child WOs in it: with workorder as ( select 'WO37342' as wonum, null as parent, 297.36 as actlabcost, 200 as actmatcost, 0 as actservcost, 0 as ...
user avatar
  • 1,377
1 vote
1 answer
55 views

Join tables and summarize rows based on condition

I have two tables. One containing countries with an ID and the geometry The other containing customers with a name, geometry and a boolean value showing if they are active or not. Countries iD ...
user avatar
0 votes
1 answer
15 views

How to get balance of of remaining medicine batch wise

I have this query: select itemcode, batchno, eXpirydate, CASE WHEN type= 1 THEN sum(qty) WHEN type= 2 THEN sum(qty*-1) END AS balance FROM ...
user avatar
  • 1
2 votes
2 answers
557 views

How to do a recursive sum of children in mysql?

I've searched and tried for long hours, and can't seem to get what I need. I have a table of accounts as a tree -parents and children-, only leaf accounts can have transactions, any parent will NOT ...
user avatar
  • 155
0 votes
1 answer
100 views

SUM and JOIN 2 columns with different tables but it duplicates results

SELECT DISTINCT [Transfer Shipment Line].[Transfer-to Code] AS [Loc Code] ,[Transfer Shipment Line].[Item No_] ,[Transfer Shipment Line].[Description] ,[Transfer Shipment Line].[Description 2] ,[...
user avatar
0 votes
1 answer
21 views

Is there a better way to summarize a table containing events of different types by identifier?

I have this table definition for sensor events: Table "public.sen_sensor_event" Column | Type | Collation | Nullable | Default ------------...
user avatar
3 votes
1 answer
531 views

Window Function - SUM() OVER (PARTITION BY ... ORDER BY ...)

I have following dataset. green: original data. yellow: values that I want to calculate. red: dummy row that is manually added to set up starting point? PureAsk = MAX(Ask - PreviousBalance, 0) ...
user avatar
1 vote
2 answers
64 views

Sum all unique values from previous dates

Let's say, for simplicity sake, I have the following table: id amount p_id date ------------------------------------------------ 1 5 1 2020-01-01T01:00:00 2 10 ...
user avatar
  • 13
1 vote
2 answers
700 views

how to sum values from subquerys

i need to collect some values from multiple tables and sum this values as a column to the result table. my query looks like this: SELECT u.ID as id, ( SELECT `meta_value` as user_name FROM `...
user avatar
  • 113
1 vote
2 answers
790 views

How to create a running balance column based on account number?

I have a stored procedure that returns account statement for one or multiple accounts Here is the part I have issues with: returning a running balance in each record based on account number. My query ...
user avatar
  • 155
2 votes
1 answer
4k views

SQL Trying to sum and group by distinct values

I have a table +-------+----------+------------+----------+---------+ | Plant | LineName | WorkCenter | Material | ProdQty | +-------+----------+------------+----------+---------+ | x | xl |...
user avatar
  • 23
0 votes
1 answer
173 views

Except command doesn't work in SQL [closed]

I am trying to make an except in SQL but it doesnt exclude the data even though they are identical. SELECT A.payment_id, SUM(A.importe_trx) monto_total FROM [dbo].[ABONOS]...
user avatar
5 votes
1 answer
124 views

SUM(Amount) different value then sum of lines - SQL Server 2017 (14.0.3045.24) [closed]

I am using following Query to get the SUM of field Amount of all lines in my filter: SELECT SUM(Amount) FROM dbo.[CompanyName$Detailed Cust_ Ledg_ Entry] WHERE [Customer No_] = 'XYZ' The result is ...
user avatar
0 votes
0 answers
382 views

Select SUM from another Select SUM result in mySQL

I have one Select SUM command that works: # SIM Orders Pending by Customers for 3101708 IMSI SIMs with 5YY MSISDNs SELECT accountname AS 'Account Name', IF ((SELECT * FROM 5YYAccounts WHERE ...
user avatar
0 votes
1 answer
82 views

How to sum a value in a grouped set of results? SQL Server

So i need to sum the value "amount" where where debit_id and transfer_id are equals: Table Example: debit_id | transfer_id | amount | debit_type 1184685 | 861288 | 25.5 | 2 1184685 | ...
user avatar
0 votes
1 answer
351 views

Mysql - get sum() result in another column [duplicate]

I get user_point column's sum like select sum(user_point) as user_total_point from users; but I also want to use that sum result for new generated column like select sum(user_point) as ...
user avatar
  • 101
1 vote
4 answers
225 views

SUM several CASE results

Given: CASE WHEN A IS NULL THEN 1 ELSE 0 END AS [A] , CASE WHEN B IS NULL THEN 1 ELSE 0 END AS [B] , CASE WHEN C IS NULL THEN 1 ELSE 0 END AS [C] How can I SUM A+B+C being the ...
user avatar
-2 votes
1 answer
31 views

Modify table by squashing rows by truncated hour

I have a table ledger which represents the ledger book. date | user_id | usd_value | eur_value ----------------------------+---------+-----------+----------- 2020-01-13 19:00:...
user avatar
  • 111
-3 votes
2 answers
98 views

Select query for group amounts per month and year (MySql) [closed]

I have a table name "transactionshistory" with columns and I would like to create a select query where a SUM the amounts per month and year for example Total_Amount | MONTH | YEAR 100 | ...
user avatar
-1 votes
1 answer
301 views

Sum for the last 5 records

Table StockSession columns: IdSeq, IdStock, DateSession, ChangePercent How can I get the sum of the ChangePercent column of the last five rows grouped by IdStock?
user avatar
1 vote
2 answers
692 views

How can I have an aggregate function in the WHERE clause?

Using PostgreSQL 9.3.24 Issue Just simple operation, but I don't know how to do that best simplest way. How can I display results only when sum of available_counts for same product_id is 0 and ...
user avatar
  • 153
0 votes
2 answers
167 views

Query returns repeating arrows when getting sum of a column grouped by some others columns

I have table Inventory and I want to get the sum(quantity) grouped by the columns gid, version, warehouse +----------+----------+-----------+----------+--------------+ | gid | version | ...
user avatar
  • 105
0 votes
2 answers
83 views

MYSQL Return lines whose sum of one field equals another tables field

I have a table of job payments, and when matching to transactions need to match up payments on the same day for the same customer that equal a single transaction for that day. I have been able to ...
user avatar
  • 3
0 votes
1 answer
77 views

Semantic of SQL Query for summing up values

I am new to SQL and not getting even simple things. I am wondering if the following code does not add a real column? select * sum(value) over (partition by prename order by NumberOfDays) SumValue ...
user avatar
0 votes
2 answers
374 views

Update with rolling sum grouped by another column

I'm trying to build a query to update a MySQL 8.0 table that looks like this ----------------------- user_id | total | delta ----------------------- 1 | 0 | 30 1 | 0 | -10 ...
user avatar
  • 101
0 votes
1 answer
93 views

Column with Default value as Sum of TIMESTAMP

I have a table which has 2 columns ( JobDate , RecordTime) -------------------------------------- JobDate | RecordTime | SumCol -------------------------------------- 2019-07-20 | 2019-07-...
user avatar
0 votes
0 answers
90 views

Nested Sum if statement with DateDiff

Problem: Need to account for delays in review process. The field "EC2WL" is when an employee submitted a case to the Work Leader for review and the field "WL1stReview" is when the Work Leader first ...
user avatar
  • 1