Is it possible to group records based on a predefined date range differences (e.g. 30 days) based on the start_date of a row and the end_date of the previous row for non-consecutive dates? I want to take the min(start_date) and max(end_date) of each group. I tried the lead and lag function with partition by in Oracle but couldn’t come up
Tag: group-by
Stuck to select maximum row
I have a table with columns: I need to select row with maximum value, and show info like I tried using group function MAX(), but I can’t select fullname, because if I use it as a GROUP BY expression, it will select max in groups. Other way, is to use WITH statement, order table by value desc, use function and
window functions and grouping – how to propagate data from row 1 through row 3 in a partition?
I have this query below: select Contact.IndividualID, Contact.IndividualID as ContactId, Contact.CaseNumber as CaseID, [Case].ProgramCode as Benefit, Contact.Email as EmailAddress, …
ORACLE SQL find row with max date for each grouping
I am trying to write a query which will return only the rows, which time has the greatest value for each id Result should look like: I tried grouping by id but I don’t know how to sort after that and pick only the top result. Answer You can use window functions: An alternative method is a correlated subquery: This
What happens when one aggregates without the ‘group by’ clause?
I was wondering why this SQL script SELECT category, avg(valuation) FROM startups group by 1; Gives me the same output like this one: SELECT category, valuation FROM startups group by 1; And why is …
How to get row count based on other column groups
I need to count the number of dates with which the invoices are made to certain customers in each month Consider the table named Table1 I tried the folllowing code and the result was The result I need should count the column named branch based on column month, the desired result is Here rowcount is based on both column branch
How to multiply all values in one column to make one number?
In SQL Server, I am trying to multiply all values of a calculated column in a table in descending date order, to produce one number. This is so I can calculate a factor that can be applied to a different table / value, i.e. multiplying every value in the “price rate” column to produce a “price factor” So far I
compute time difference between rows and conditional group by using PostgreSQL
I have data like as shown below: +——-+——-+ | index | time | +——-+——-+ | 1 | 09.00 | | 2 | 09.02 | | 3 | 09.03 | | 4 | 09.05 | | 5 | 09.11 | | 6 | 09.12 | | …
Group rows by the same value in the field, while matching on partial value only
I have a table that has many rows (between a few 1000s to a few million). I need my query to do the following: group results by the same part of the value in the field; order by the biggest group first. The table has mostly values that have only some part are similar (and i.e. suffix would be different).
merge values in select
I have next part of query: SELECT types, count FROM … Result is next: types count soft 3 lite soft 2 middle soft 7 hard soft 2 other 5 what I need is to merge results …