id amount 1 20 2 20 3 20 4 30 5 100 6 20 7 30 8 100 I would like to create three more columns which will be calculated by the amount column. Expected output: id amount openamt debamt credamt closeamt 1 20 340 140 200 400 2 20 340 140 200 400 3 20 340 140 200 400
Tag: sql
Count ROW type item Athena / Presto
I have an Athena query like this and the result is I would like to count the number of records per day per devices to have a result like this EDIT My dataset is actually like this Here the expected results would be : Answer You can cast your json to map and count number of keys: Output: device_id date
Problem Bigquery stored procedure not filtering
I’m trying to use a stored procedure to get some data from my clients filtering a table by date and channel of purchase in a query that looks something like this: After running the procedure, I use the following call: The call executes and returns a table, but the result is never filtered. The dates don…
How can i store results of a query in an array?
I have to store last 5 employees_id’s from employees table into an array. I made the query correct and i have the array, but i can’t understand the syntax to store the results in that array. Here’s my code and here’s my query How can i store the results from the query in the array? As …
How to query cumulative sum period by month
I have an issue with table source like this: Available month in table is only as is from column month, no April, May, June etc. I need to create a view to create “Year to Date” table as shown here: I need to view all months from Year 2020 January – December with value based on table source. …
Search by best values for every minute
The problem I’m having is that I have a table of data that has a new row added every second (imagine the structure {id, timestamp(datetime), value}). I would like to do a single query for MSSQL to go through the table and output only the number of objects that have the top 2 values asc for each minute (…
Can “value in list or list is empty” be written shorter?
Given this SQL: Is there a way to write the “in list or list is empty” part shorter? Preferably in a way that contains the list only once (see the Don’t_repeat_yourself principle ) I’m interested for Oracle SQL or PL/SQL, but other information is also welcome. As requested, a MRE that …
Update column of a table with new foreign key of associated table
Let’s say I have a Persons and Books table that were associated. Currently persons.usercode serves as the primary key and hence the foreign key on associated tables. I would like to change the primary key of the persons table to persons.uid. So now I want the books table to look like Dropping and adding…
T-SQL Logic (Where exists one column but not the other)
I need a bit of logical help in writing a query. Here is the set up. I need a query that returns all records from main_table where the id of main_table matches the id of sub_table, but seq of main_table does not match seq of sub_table. Expected result is id seq A1 3 A2 2 A2 3 My attempt What
Laravel many to many relastionship count
I have two tables. User and days, with many to many relationship. user_days : user_id, day_id I want to get the count number of users in day_id = 1 {for a particular day} or something like this. Any way to get count from pivot table? This function didnot work. the table structure ? How do I find the user coun…