Questions tagged [sum]
The sum tag has no usage guidance.
171
questions
0
votes
0
answers
77
views
Counting Rows From a Value in a SQL text box - please please can anyone help?
I just can't find an example of this anywhere and hoping someone can help.
I have a query which returns a list of care services and other fields based on the care service table values and calculated ...
0
votes
0
answers
23
views
How to Join two tables with SUM in SQL server [duplicate]
i have 2 tables
1.registros
2.servicios
what I wanna have is this: id,nombre,apellido,direccion,telefono servicios_pedidos of table 1 + sum of table 2 servicion.precios with table 1 registros.[...
- 1
0
votes
1
answer
48
views
Mariadb vs Mysql: Sum aggregate select with outer query column fails in MariaDB
I am having an issue migrating from MySQL to MariaDB 10.11.
My query gets an order by an id and calculates the balance for the order by summing the transactions/taxes and discounts via a select ...
2
votes
2
answers
69
views
How to sum two different queries and group them?
amount
from_client
to_client
transaction_type
200
NA
Jacques
tsa
400
John
NA
tsb
800
John
NA
tsb
800
NA
Jacques
tsa
100
Jacques
NA
tsb
I would like to get the sum of Jacques and John ...
- 33
2
votes
1
answer
26
views
mysql count of a column based on date and type
I have the following table optimizations
select items_type,
created_at,
count_optimized_items
from optimizations
where shop_id=126
and count_optimized_items!=0
items_type
created_at
...
- 23
2
votes
2
answers
77
views
SQL Sum with break-out (audits on inventory)
I'm having some difficulty getting this mysql query to work. I have the following tables
create table items (
item_id int not null auto_increment,
item_name varchar(25),
primary key (...
- 23
0
votes
1
answer
22
views
merging 2 Queries into one
I have the following queries:
Query 1:
SELECT agencies.code,
SUM(IF(commissions.action = 'collection', commissions.commission, 0)) as collections,
SUM(IF(commissions.action = 'delivery',...
- 63
1
vote
1
answer
26
views
MySQL Query - Sum data according to Category Parent name - each their own column
Here is the data I have:
table agencies:
id
code
18
agency_01
6
agency_02
table compensations
id
agency_id
compensation_category_id
amount
registered_at
3
6
4
45000.00
2023-01-01
4
6
5
25000.00
...
- 63
3
votes
3
answers
1k
views
Join twice on same table with different conditions and no PK / FK relations
I have the following tables:
agencies: | code |
commissions: | registered_at | action | origin | destination | commission |
I need to SUM(commissions.commission) for the following conditions
...
- 63
-1
votes
1
answer
579
views
How to list sum of values in the specific period
Sorry for very newbie questions. I have a table, shown below with values about duration of events. Sometimes I have several events in one hour/day. Instead of showing each particular event, I would ...
2
votes
2
answers
47
views
create list of payees with 401k and roth contributions and sum matching amounts
I'm using sql-server. I have a table of 401k and roth contributions plus matching. I need a query that will produce a list of payees with their contribution(s) listed under the correct column with ...
- 21
0
votes
1
answer
86
views
MYSQL - return the minimum number of records so price sum is possible to be as close to the input price
I have a table with names, prices and a user input price, for example, 100.
I want to return a minimum number of records so prices sum will be close to the input price
So If I have in records items ...
- 1
0
votes
1
answer
112
views
Improving Performance of Group By sum Query to get payment data based on total Employees
I have a query to show payment data paid by grouped total Employee (1,2,3) (their size [how much number of users are in total Employee)) and the total amount.
But My query took 30-35 seconds
result ...
- 105
0
votes
2
answers
59
views
How to substruct some table rows from another related table rows and get the SUM of the totale result
I have two table (Orders, Payments)
they are 1 to many relationships (1:N)
One order can have many payments
Table 1: Orders
id
total_price
1
1000
2
2000
3
3000
Table 2: Payments
order_id(FK of ...
0
votes
0
answers
173
views
select by aggregation of fields in json column and sum
Using postgresql 11.
Given the following dataset:
foo | bar
-------------------+--------------------------------------------------
"a" | {'a_count': 1, '...
- 101
-1
votes
1
answer
3k
views
SQL Query duplicates sum for each join
I'm trying to do this table which will give me a date, and then sum up sales from a separate table, but the sales values get multiplied 14 times for some reason. Will this need a temp table, or am I ...
- 11
-4
votes
1
answer
888
views
How select join sum with 0 returned if not exist
I need some advice. I want to select join with sum.
select year(date), month(mon), c.id, c.name, sum(t.trx_detail)
from client c
left join trx t
on c.id = t.id
group by year(date), month(mon), c.id, ...
0
votes
1
answer
701
views
Add new column based on conditions and the value difference of other column
I think I might be overlooking something obvious here. I'm not asking someone to write the whole code for me, I just need a hint or a link to a similar case.
Thanks in advance...
My query:
select ...
- 127
-2
votes
2
answers
55
views
How sum over work
Given this table:
CREATE TABLE Table1
(
[Classroom] int,
[CourseName] varchar(8),
[Lesson] varchar(9),
[StartTime] char(4),
[EndTime] char(4)
);
then:
INSERT INTO Table1
(
[Classroom],
...
- 107
0
votes
2
answers
274
views
Agregation group by date or group, pair index, MySQL, slow response
I would like to get an advice from you about manipulating lot of data using SUM and GROUP BY two columns.
I have a table with 60M rows:
mysql> SELECT COUNT(*)
-> FROM adgroup_ad_diff;
+-----...
- 21
0
votes
1
answer
833
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", "...
3
votes
2
answers
22k
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
...
0
votes
2
answers
6k
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`) ...
0
votes
1
answer
1k
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 `...
1
vote
1
answer
589
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 ...
- 21
2
votes
2
answers
2k
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 ...
- 23
0
votes
0
answers
70
views
SUM `amount` column based on `transactionType` in Postgresql "transactions" table
I have this table:
+--------+-----------------+
| amount | transactionType |
+--------+-----------------+
| 10 | Debit |
| 5 | Credit |
| 15 | Credit |
| 20 ...
- 101
-1
votes
1
answer
36
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 ...
- 101
0
votes
3
answers
1k
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,
...
- 103
2
votes
2
answers
119
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 ...
- 1,437
0
votes
1
answer
45
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 ...
2
votes
2
answers
77
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
---------- --------- --------- ------ --------- -----...
- 123
3
votes
2
answers
431
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 ...
- 1,437
0
votes
1
answer
350
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=...
- 47
0
votes
1
answer
2k
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 ...
- 1
1
vote
1
answer
211
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 ...
- 13
1
vote
3
answers
5k
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 ...
- 193
0
votes
2
answers
2k
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 | ...
- 35
1
vote
2
answers
208
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
...
- 13
1
vote
2
answers
120
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 ...
- 25
0
votes
1
answer
2k
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 ...
- 103
4
votes
1
answer
301
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 ...
- 76
0
votes
1
answer
45
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 ...
0
votes
1
answer
23
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 ...
- 1,437
1
vote
1
answer
90
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 ...
0
votes
1
answer
16
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 ...
- 1
2
votes
2
answers
953
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 ...
- 165
0
votes
1
answer
109
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]
,[...
- 21
0
votes
1
answer
46
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
------------...
- 103
3
votes
1
answer
1k
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)
...
- 53