I need to calculate a running total but need to reset the total on a condition (when expected reached = 0 and product_group and product changes). Got help here without the two extra fields: Calculate a running total with a condition in BigQuery I have this table and may use product_group and product as integers or strings as below. Any
Tag: window-functions
how to comparison LAG function in mysql
I got complicated problem in mysql. I have some table on mydatabase. sample docs(this is only simple sample, actual data are so many table that I have to join) table “merchant” table “transaction” I want to know the information of merchants transaction daily “before” and “after” to comparison like this below I tried with this query but that query didn’t
How to use Lead() and Lag() to get the first non null value
I have the following query and returns this table: now, what I’m trying to do is to calculate the lead of the first non null value on the field. for example, for event_id = 138466815 the columnCount is 85. I want the “lead1” field to show 82.53, which is the next non null value below. and that then later, for
put a 1 if the customer has bought in 3 following months sql assistant
I’m a beginner with teradata SQL assistant and I don’t know if it can do what I need. I have a base with the variables ID, month (or period) and the incomes of that month. What I need is to put a 1 if …
query mysql to find total value
please help me. imagine the table as bellow and I want to query and show result like this I dont know how to write query to show result like that. I used (date_join) as dates, count(id) and group by date(date_join) and the result not show like that. Answer In MySQL 8.0, you can solve this with aggregation and a window
SQL statement – get 1st minimum then 2nd minimum from join
sorry for terrible description but I have struggled to word this My 2 tables are like this tblParent.. OrderID Customer 789 Bob tblChild OrderID SortOrder Price 789 1 20.00 …
Count rows with same value while keeping columns
Lets say i have the following sql table: How can i modify my select query to return my data with an additional column telling me the number of occurrences for each ID, while keeping the original columns collected? Which would result in this: Query is this: Answer You can use window functions, if your database supports them. What you ask
Using window function in redshift to aggregate conditionally
I have a table with following data: Link to test data: http://sqlfiddle.com/#!15/dce01/1/0 I want to aggregate the items column (using listagg) for each group in gid in sequence as specified by seq column based on the condition that aggregation ends when pid becomes 0 again for a group. i.e. for group g1, there would be 2 aggregations; 1 for seq
What is the best way in SQL to combine sequential events based on matching end time to start time?
That database I work in records events based on a part ID and the times in which it is active. The issue I came across is these events are truncated to fit within a single day. If the active time for a part carries over to the next day, the event will be split by the number of days it
Calculate interval between boolean column change
I have a table with measurements of weather here is a simplified version of it: and I need to calculate how much time is been sunny. I know I need to use window functions but I’m stuck. Because I need this interval to be calculated in some range for example for last day. I have been able to make a