# Tag Info

### 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 ...
• 97.9k

### 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) ...
• 23k
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 ...
• 180k
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 ...
• 97.9k
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(...
• 4,401

### 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 ...
• 1,959
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 ...
• 23k

### 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 ...
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 ...
• 31.3k
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()) ...
• 166

### 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, ...
• 97.9k

### 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 (...
• 16.4k
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 ...
• 84k
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 ...
• 37.5k
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 ...
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 ...
• 32.2k
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, ...
• 97.9k

### 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 ...
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. ...
• 11.6k

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 ...
• 141
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
• 19.9k
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 ...
• 12.8k

### 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 ...
• 4,401

### 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 ...
• 4,338

### 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 ...
• 2,507
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 ...
• 11.6k

### 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 ...
• 4,647

### 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].[...
• 1,144