I have a table for machine installations in this way: I need a query that returns the number of installed machines per day. Like this: I know that given a date, say ‘2020-01-03’, I can get the number of installed machines as follows: However, I do not know how to query in such a way I can get the …
Passing parameters for a constraint function
I have the following table that joins driver and truck tables in order to assign trucks to drivers. I need to constraint entries that belong to the same driver on the same day, and DO NOT include the same truck code. It was suggested to create a boolean function to pass along dateval, truckval and driverval, …
How can you group data by multiple fields with separate totals in SQL or Power query
I have a set of data that I want to count by multiple characteristics. I am starting with something likes this (True/False for catagories) week PP SEN 1 T F 1 T T 1 F F 2 T F 2 T T 2 F F 2 F F 3 T F 3 F F I want to end up with
How to get data for missing weeks in Summarised data
I have two tables stores_data and financial_week as shown below. Stores data is a summarised data across multiple attributes. My task is to generate data for all the weeks present in the second table, if data is missing, the quantity should be listed as 0. Expected Result set is this – I have done cross…
How to use the results of a query to filter a table comparing one field to the result?
I want to filter a table showing only the rows where total is between ± 3 standard deviations from the average. The query I’m using is this: But I’m getting the error: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the …
Oracle trigger multiple conditions in when clause
I’m trying to create a trigger that updates a column in a table when other columns are updated. but getting the following error while saving the trigger ORA-25000: invalid use of bind variable in trigger WHEN clause My trigger is as follows, I’m not sure what is wrong with the code. Answer Althoug…
Hive Explode the Array of Struct key: value:
This is the below Hive Table And this is the data in the above table- Is there any way I can get the below output using HiveQL? I tried use explode() but I get result like that: Answer Use laterral view [outer] inline to get struct elements already etracted and use conditional aggregation to get values corres…
Weird behavior of MySQL Server when ordering by 2 conditions
Say a database table my_table has the following structure: and some of the rows in the database have total_votes == 0. I want to get the rows ordered like this, except I also want to include the rows that have total_votes == 0. I tried using the following query, and it worked like a charm: My question is why …
Convert list of dictionaries into dictionary in Postgres jsonb
I have an actions_table looking like: I would like to aggregate by profile_id and jsonify as: The closest I could get to is: By means of the query: How do I get to my desired result from here (convert list of dicts into dict), or how do I fix my original query? CAVEATS I need a general answer, I might
Iterate through query
I’m trying to iterate through simple query This is what I managed to create But I get: Answer You don’t need a while loop for this. You can do this in a single query. One method is: Trying to use a while look is troublesome. Such programming constructs would be in programming blocks. They are desi…