10 votes

Compute the total of SUM(column)

This is valid syntax, too: sum(sum(qty)) over () It's a bit confusing when one sees it at first but you only have to remember that the window functions - e.g. sum() over () - are applied ...
ypercubeᵀᴹ's user avatar
10 votes

Sum Interval of Dates within same column

You can use the LEAD analytic function to obtain the next row's eventtype and eventdate alongside the current row's data: SELECT eventtype, eventdate, LEAD(eventtype) OVER (ORDER BY eventdate) ...
Andriy M's user avatar
  • 23k
9 votes
Accepted

Compute the total of SUM(column)

CREATE TABLE #foo ( itemid int, proddte date, qty int ); INSERT #foo(itemid,proddte,qty) VALUES (1,'20140101',5),(1,'20140102',7),(2,'20150101',10); -- if it really needs to be a column with the ...
Aaron Bertrand's user avatar
9 votes
Accepted

SQL Query for cumulative sum

You need a "window" aggregate, i.e. an OVER clause in the aggregate. And because the query already has a GROUP BY, the aggregate needed is the SUM() over the COUNT(d.FK_User) you already have: SELECT ...
ypercubeᵀᴹ's user avatar
9 votes
Accepted

How to use JOIN and SUM function to Calculate the total of query returned column?

You can use grouping set also declare @JobRequiredProducts table (jobId int, productId int); declare @Product table(productId int, productUnitPrice decimal(8,2)) insert into @JobRequiredProducts(...
Sabin B's user avatar
  • 4,401
7 votes

SUM() ignores GROUP BY and sums up 4 rows instead of 2

I believe you have a misunderstanding of what GROUP BY does. Not surprising, I had issues myself when first learning, in large part because the MySQL manual doesn't actually explicitly say what GROUP ...
Willem Renzema's user avatar
7 votes
Accepted

How do you SUM a column without having its name?

Many (perhaps, most) other database products will not allow you to define a derived table with a calculated column that has no explicitly assigned alias. In other words, they will not let you have ...
Andriy M's user avatar
  • 23k
6 votes

Join Multiple Tables for Aggregates

This request is quite old, but as the accepted answer is wrong, I thought I'd add a correct one, so future readers don't get too confused. A campain has landers and conversions. If we merely join all ...
Thorsten Kettner's user avatar
5 votes
Accepted

How to display queries of separate tables in adjacent columns?

You are basically building two tables, which then has to be joined on the date column. In your query you miss the join condition. One clear way of solving this is building the two result sets as ...
András Váczi's user avatar
5 votes
Accepted

Total hours worked per employee per day including overnight sessions

Please check if the performance is sufficient: SELECT empid ,SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, time, (SELECT IFNULL(MIN(time),NOW()) ...
Olli's user avatar
  • 166
5 votes

How do you SUM a column without having its name?

Another way to add aliases (for derived tables and their columns) in standard SQL would be (unfortunately it hasn't been implemented in Oracle): select sum(count_employees) as total_count, ...
ypercubeᵀᴹ's user avatar
5 votes

Performance issues summing a count column over a date range

I can't speak for the query accessing the view but the samples you provided give a quick insight into the performance of the view. You are getting a filter operator at the end of all of your joins (...
Randi Vertongen's user avatar
5 votes
Accepted

Zero/NULL Case Trick

It is variously known as: conditional sum filtered aggregation conditional aggregation selective aggregation It may be either written explicitly using CASE or equivalently using FILTER syntax ...
Paul White's user avatar
  • 84k
5 votes
Accepted

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

This can be accomplished simply with a JOIN and GROUP_BY clause like so: SELECT p.designation, SUM(ISNULL(sp.quantity, 0)) AS [total quantity] FROM product AS p LEFT JOIN sub_product AS sp ...
J.D.'s user avatar
  • 37.5k
4 votes
Accepted

Total hours worked per employee per day

If we assume that login and logout always comes in pairs, and that login and logot are always on the same date, it's just a matter of finding the smallest time for a logout after each login. If no ...
Lennart - Slava Ukraini's user avatar
4 votes
Accepted

Rolling SUM with a 30 day range

I haven't tested this extensively but I think it can do what you need. Here I assume that ID_NO is a customer identifier, TD is the date of the order, and AMT_PUR represents the amount ordered. You ...
Joe Obbish's user avatar
  • 32.2k
4 votes
Accepted

Insert characters into entire row and union with continue of row_number

You could use GROUP BY GROUPING SETS and the GROUPING() function: SELECT idx = ROW_NUMBER() OVER (ORDER BY GROUPING(a_id), a_id), a = CASE WHEN GROUPING(a_id) = 0 THEN a_id ELSE 'Total' END, ...
ypercubeᵀᴹ's user avatar
4 votes

How to display queries of separate tables in adjacent columns?

Using @dezso's solution, which is the accepted answer above, here is the amended query and result for reference. WITH pc AS (SELECT date_trunc('day', start_time - INTERVAL '18 hours')::date as ...
Alex Willison's user avatar
4 votes
Accepted

How can I get this "rolling total" query to sum correctly?

The problem with your query is simply that you're not actually processing the records in the order you want to. Your query picks up all records with an idestado_cliente less than the current row. ...
RDFozz's user avatar
  • 11.6k
4 votes

Sum(JSON_EXTRACT) MariaDB 10.3.11

Another way to solve this would be by using Recursive Common Table Expressions which is available since MariaDB 10.2.2. This solution does not need a numbers table but recursively iterates the result ...
cebe's user avatar
  • 141
4 votes
Accepted

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

It seems you need in simple SELECT *, SUM(debit_balance - credit_balance) OVER (PARTITION BY account_no ORDER BY id) balance FROM acc_account_transactions fiddle
Akina's user avatar
  • 19.9k
4 votes
Accepted

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

You don't need UNION, you can do this in one query. It's just a matter of picking your grouping values carefully: SELECT [Date], FirstName = CASE WHEN Item <> 'Breakfast' THEN FirstName ...
Charlieface's user avatar
  • 12.8k
4 votes

create list of payees with 401k and roth contributions and sum matching amounts

You can use a CASE expression to return the desired output, like this: SELECT t.payee_id, SUM(CASE WHEN t.contrib_type='4' THEN t.contribution ELSE 0 END) as contrib_401k, SUM(CASE ...
Sabin B's user avatar
  • 4,401
3 votes

SUM( A + B ) vs SUM(A) + SUM(B)

For the InnoDB/MySQL 5.6 table with 30M+ rows of FLOATs result is the next. select SUM(x + y) from points; /* Affected rows: 0 Found rows: 1 Warnings: 0 Duration for 1 query: 8,596 sec. */ select ...
Kondybas's user avatar
  • 4,338
3 votes

MySQL: SELECT with SUM() and COUNT() as new columns

If we assume that 'QtyOfParts' and 'FamilyID' are existing columns in the table and furthermore, that the other 2 columns are "calculated", then maybe the following query would be helpful (creating ...
stefan's user avatar
  • 2,507
3 votes
Accepted

SUM() ignores GROUP BY and sums up 4 rows instead of 2

As Willem Renzema said, you've misunderstood how GROUP BY works. Since it doesn't seem like you've understood what he said, let me try saying it a little differently. GROUP BY, logically enough, is ...
RDFozz's user avatar
  • 11.6k
3 votes

Compute the total of SUM(column)

One possible way around is to wrap the first GROUP BY into CTE: WITH CTE AS ( select itemid ,proddte ,sum(qty) AS SumQty from testtable where .... group by ...
Vladimir Baranov's user avatar
3 votes

SUM CASE WHEN CLAUSE

Assuming that the Ageing values match the names of the columns in your expected output, then this should work: SELECT * FROM ( SELECT BU, Ageing, NetDue FROM [dbo].[...
mathewb's user avatar
  • 1,144
3 votes
Accepted

query SUM function issue/ grouping issue

Use your main query as a subquery: SELECT EmployeeNumber, JobCode, [Date], SUM(Hours) as [Hours] FROM ( SELECT tServiceReps.EmployeeNumber, tServiceHours....
McNets's user avatar
  • 23.7k

Only top scored, non community-wiki answers of a minimum length are eligible